Ad

How To Import CSV To An Table On BigQuery Using Properly

I have a python script that execute a gbq job to load a csv file f to table on BigQuery. all data writes to one column but i want it to be loaded it in each column.i am tried autodetect but it did not help too. my csv:

id,first_name,username,last_name,chat_username,chat_id,forward_date,message_text
231125223~Just~koso~swissborg_bounty~-1001368946079~1517903147~test
481895079~Emerson~EmersonEmory~swissborg_bounty~-1001368946079~1517904387~picture
316560356~Ken Sam~ICOnomix~swissborg_bounty~-1001368946079~1517904515~Today 

this is my code:

from google.cloud.bigquery import Client
import os
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = '***.json'
os.environ['GOOGLE_CLOUD_DISABLE_GRPC'] = 'True'

from google.cloud import bigquery
dataset_name = 'test_temporary_dataset'
table_name='table_telega'
bigquery_client = bigquery.Client()
dataset = bigquery_client.dataset(dataset_name)
table = dataset.table(table_name)
#table.reload()
job_config = bigquery.LoadJobConfig()
job_config.source_format = 'text/csv'
job_config.skip_leading_rows = 1
job_config.autodetect = True

job_config.allow_jagged_rows=True
job_config.allow_quoted_newlines=True
job_config.fieldDelimiter='~'
with open('tele2.csv', 'rb') as source_file:
    #job = table.upload_from_file(source_file, source_format='text/csv')
    job=bigquery_client.load_table_from_file(source_file, table, job_config=job_config)

job.result()

and this is what i get : bigquery

how to load csv properly column by column

Ad

Answer

Autodetect can only work with:

  1. comma
  2. pipe
  3. tab

Tilde won't work with autodetection. You'll need to specify the schema, or change your delimiters to one of the above.

See here for more info.

Ad
source: stackoverflow.com
Ad