title: What is an efficient way to upload csv files from a VM to bigquery
body: <p><strong>Question</strong></p>
  
  <p>Is there a more efficient way I can use to streamline a process of uploading csv files to bigquery from a python script or any other way?</p>
  
  <p><strong>Description</strong></p>
  
  <p>I have 1528596 CSV files which needs to be uploaded to bigquery [Tables are created already]. My current approach is proven to be <a href="https://i.stack.imgur.com/WTiXf.png" rel="nofollow noreferrer">slow</a>, which I think is  due to google bigquery <a href="https://cloud.google.com/bigquery/quotas#api_requests" rel="nofollow noreferrer">upload quotas</a>. Going above the <a href="https://cloud.google.com/bigquery/quotas#api_requests" rel="nofollow noreferrer">quota</a> gives me the following exceptions:</p>
  
  <pre><code>Traceback (most recent call last):
    File “name_of_file.py", line 220, in &lt;module&gt;
    File "name_of_file.py", line 122, in upload_csv_to_bigquery
      job.result()  # Waits for table load to complete.
    File "/home/bongani/.local/lib/python3.6/site-packages/google/cloud/bigquery/job.py", line 660, in result
      return super(_AsyncJob, self).result(timeout=timeout)
    File "/home/bongani/.local/lib/python3.6/site-packages/google/api_core/future/polling.py", line 120, in result
      raise self._exception
  google.api_core.exceptions.Forbidden: 403 Quota exceeded: Your project exceeded quota for imports per project. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors
  </code></pre>
  
  <p>I have emailed google support to try to increase the quota but they replied and said, they are unable to.</p>
  
  <p><strong>My current implementation:</strong></p>
  
  <pre><code>import os
  import time
  from concurrent.futures import ProcessPoolExecutor, as_completed
  
  from google.cloud import bigquery
  from google.cloud.bigquery import LoadJobConfig
  
  root_dir = "/path/to/some/directory"
  dataset_id = 'dataset_namex'
  
  bigquery_client = bigquery.Client()
  
  
  def upload_csv_to_bigquery(table_name, csv_full_path):
      s = time.time()
      load_config = LoadJobConfig()
      load_config.skip_leading_rows = 1
      table_ref = bigquery_client.dataset(dataset_id).table(table_name)
      with open(csv_full_path, 'rb') as source_file:
          job = bigquery_client.load_table_from_file(source_file, table_ref, job_config=load_config)  # API request
          job.result()  # Waits for table load to complete.
      print(f"upload time: {time.time() - s}")
  
  
  def run():
      with ProcessPoolExecutor(max_workers=30) as process_executor:
          futures = []
          for csvfile in os.listdir(root_dir):
              table_name = csvfile.split('_')[-1]
              futures.append(process_executor.submit(upload_csv_to_bigquery, table_name, root_dir + csvfile))
          for future in as_completed(futures):
              future.result()
      print("DONE!!!")
  
  
  run()
  </code></pre>
  
  <p>This image shows the number of requests I make per sec, uploading. <a href="https://i.stack.imgur.com/WTiXf.png" rel="nofollow noreferrer">Metrics from Google Cloud Platform</a></p>