title: Efficiently write a Pandas dataframe to Google BigQuery
body: <p>I'm trying to upload a <code>pandas.DataFrame</code> to Google Big Query using the <code>pandas.DataFrame.to_gbq()</code> function documented <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_gbq.html#pandas-dataframe-to-gbq" rel="noreferrer">here</a>. The problem is that <code>to_gbq()</code> takes 2.3 minutes while uploading directly to Google Cloud Storage takes less than a minute. I'm planning to upload a bunch of dataframes (~32) each one with a similar size, so I want to know what is the faster alternative.</p>
  <p>This is the script that I'm using:</p>
  <pre><code>dataframe.to_gbq('my_dataset.my_table', 
                   'my_project_id',
                   chunksize=None, # I have tried with several chunk sizes, it runs faster when it's one big chunk (at least for me)
                   if_exists='append',
                   verbose=False
                   )
  
  dataframe.to_csv(str(month) + '_file.csv') # the file size its 37.3 MB, this takes almost 2 seconds 
  # manually upload the file into GCS GUI
  print(dataframe.shape)
  (363364, 21)
  </code></pre>
  <p>My question is, what is faster?</p>
  <ol>
  <li>Upload <code>Dataframe</code> using <code>pandas.DataFrame.to_gbq()</code> function</li>
  <li>Saving <code>Dataframe</code> as CSV and then upload it as a file to BigQuery using the <a href="https://cloud.google.com/bigquery/docs/loading-data-local#loading_data_from_a_local_data_source_using_the_api" rel="noreferrer">Python API</a></li>
  <li>Saving <code>Dataframe</code> as CSV and then upload the file to Google Cloud Storage using <a href="https://stackoverflow.com/a/36331938/5318634">this procedure</a> and then reading it from BigQuery</li>
  </ol>
  <p><strong>Update:</strong></p>
  <p><strong>Alternative 1 seems faster than Alternative 2</strong> , (using <code>pd.DataFrame.to_csv()</code> and <code>load_data_from_file()</code> <code>17.9 secs more in average with 3 loops</code>):</p>
  <pre><code>def load_data_from_file(dataset_id, table_id, source_file_name):
      bigquery_client = bigquery.Client()
      dataset_ref = bigquery_client.dataset(dataset_id)
      table_ref = dataset_ref.table(table_id)
      
      with open(source_file_name, 'rb') as source_file:
          # This example uses CSV, but you can use other formats.
          # See https://cloud.google.com/bigquery/loading-data
          job_config = bigquery.LoadJobConfig()
          job_config.source_format = 'text/csv'
          job_config.autodetect=True
          job = bigquery_client.load_table_from_file(
              source_file, table_ref, job_config=job_config)
  
      job.result()  # Waits for job to complete
  
      print('Loaded {} rows into {}:{}.'.format(
          job.output_rows, dataset_id, table_id))
  </code></pre>