How to upload a local CSV to google big query using python

Question

I'm trying to upload a local CSV to google big query using python

def uploadCsvToGbq(self,table_name):


    load_config = {
    'destinationTable': {
    'projectId': self.project_id,
    'datasetId': self.dataset_id,
    'tableId': table_name
    }
    }

    load_config['schema'] = {
    'fields': [
    {'name':'full_name', 'type':'STRING'},
    {'name':'age', 'type':'INTEGER'},
    ]
    }
    load_config['sourceFormat'] = 'CSV'

    upload = MediaFileUpload('sample.csv',
                     mimetype='application/octet-stream',
                     # This enables resumable uploads.
                     resumable=True)
    start = time.time()
    job_id = 'job_%d' % start
    # Create the job.
    result = bigquery.jobs.insert(
    projectId=self.project_id,
    body={
    'jobReference': {
    'jobId': job_id
    },
    'configuration': {
    'load': load_config
    }
    },
    media_body=upload).execute()

    return result

when I run this it throws error like

"NameError: global name 'MediaFileUpload' is not defined"

whether any module is needed please help.

stackoverflow.com/questions/25048787/… I tried this example — Teejay
– Teejay, Commented Mar 24, 2016 at 9:16

Samihan Jawalkar · Accepted Answer · 2021-04-05 14:57:21Z

5

One of easiest method to upload to csv file in GBQ is through pandas.Just import csv file to pandas (pd.read_csv()). Then from pandas to GBQ (df.to_gbq(full_table_id, project_id=project_id)).

import pandas as pd
import csv
df=pd.read_csv('/..localpath/filename.csv')
df.to_gbq(full_table_id, project_id=project_id)

Or you can use client api

from google.cloud import bigquery
import pandas as pd
df=pd.read_csv('/..localpath/filename.csv')
client = bigquery.Client()
dataset_ref = client.dataset('my_dataset')
table_ref = dataset_ref.table('new_table')
client.load_table_from_dataframe(df, table_ref).result()

edited Apr 5, 2021 at 14:57

answered Dec 24, 2018 at 5:08

Samihan Jawalkar

1131 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

igorkf Over a year ago

I had to install pandas_gbq to be able to use to_gbq method.

Guest5489 · Accepted Answer · 2017-05-23 07:01:04Z

2

pip install --upgrade google-api-python-client

Then on top of your python file write:

from googleapiclient.http import MediaFileUpload

But care you miss some parenthesis. Better write:

result = bigquery.jobs().insert(projectId=PROJECT_ID, body={'jobReference': {'jobId': job_id},'configuration': {'load': load_config}}, media_body=upload).execute(num_retries=5)

And by the way, you are going to upload all your CSV rows, including the top one that defines columns.

edited May 23, 2017 at 7:01

answered May 22, 2017 at 15:51

Guest5489

213 bronze badges

Comments

Michael Sheldon · Accepted Answer · 2016-03-25 18:15:42Z

0

The class MediaFileUpload is in http.py. See https://google-api-python-client.googlecode.com/hg/docs/epy/apiclient.http.MediaFileUpload-class.html

answered Mar 25, 2016 at 18:15

Michael Sheldon

2,06711 silver badges8 bronze badges

1 Comment

Praxiteles Over a year ago

This link is now broken.

Collectives™ on Stack Overflow

How to upload a local CSV to google big query using python

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related