在这里，我们将给大家分享关于高效地将Pandas数据框写入GoogleBigQuery的知识，让您更了解pandas写入数据库的本质，同时也会涉及到如何更有效地BigQuery和与Google表格的连

在这里，我们将给大家分享关于高效地将Pandas数据框写入Google BigQuery的知识，让您更了解pandas写入数据库的本质，同时也会涉及到如何更有效地BigQuery 和与 Google 表格的连接失败、Google Bigquery 时间戳自动插入、Google BigQuery 流与执行 SQL 查询 - 定价差异、Google BigQuery——企业级大数据分析工具的内容。

• 高效地将Pandas数据框写入Google BigQuery（pandas写入数据库）
• BigQuery 和与 Google 表格的连接失败
• Google Bigquery 时间戳自动插入
• Google BigQuery 流与执行 SQL 查询 - 定价差异
• Google BigQuery——企业级大数据分析工具

高效地将Pandas数据框写入Google BigQuery（pandas写入数据库）

我正在尝试pandas.DataFrame使用此处pandas.DataFrame.to_gbq()记录的功能将一个上传到Google大查询。问题是需要2.3分钟，而直接上传到Google
Cloud Storage
GUI需要不到一分钟。我打算上传一堆大小相似的数据帧（〜32个），所以我想知道它是更快的替代方法。to_gbq()

这是我正在使用的脚本：

dataframe.to_gbq(''my_dataset.my_table'',                  ''my_project_id'',                 chunksize=None, # i''ve tryed with several chunksizes, it runs faster when is 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 GUIprint(dataframe.shape)(363364, 21)

我的问题是，什么更快？

1. Dataframe使用pandas.DataFrame.to_gbq()功能上传
2. 另存Dataframe为csv，然后使用Python API作为文件上传到BigQuery
3. 另存Dataframe为csv，然后使用此过程将文件上传到Google Cloud Storage ，然后从BigQuery读取文件

更新：

替代如图2所示，使用pd.DataFrame.to_csv()和load_data_from_file()似乎比替代1至需要更长的时间（17.9秒更平均以3个循环）：

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))

谢谢！

答案1

我Datalab使用以下代码对备选方案1和3进行了比较：

from datalab.context import Contextimport datalab.storage as storageimport datalab.bigquery as bqimport pandas as pdfrom pandas import DataFrameimport time# Dataframe to writemy_data = [{1,2,3}]for i in range(0,100000):    my_data.append({1,2,3})not_so_simple_dataframe = pd.DataFrame(data=my_data,columns=[''a'',''b'',''c''])#Alternative 1start = time.time()not_so_simple_dataframe.to_gbq(''TestDataSet.TestTable'',                  Context.default().project_id,                 chunksize=10000,                  if_exists=''append'',                 verbose=False                 )end = time.time()print("time alternative 1 " + str(end - start))#Alternative 3start = time.time()sample_bucket_name = Context.default().project_id + ''-datalab-example''sample_bucket_path = ''gs://'' + sample_bucket_namesample_bucket_object = sample_bucket_path + ''/Hello.txt''bigquery_dataset_name = ''TestDataSet''bigquery_table_name = ''TestTable''# Define storage bucketsample_bucket = storage.Bucket(sample_bucket_name)# Create or overwrite the existing table if it existstable_schema = bq.Schema.from_dataframe(not_so_simple_dataframe)# Write the DataFrame to GCS (Google Cloud Storage)%storage write --variable not_so_simple_dataframe --object $sample_bucket_object# Write the DataFrame to a BigQuery tabletable.insert_data(not_so_simple_dataframe)end = time.time()print("time alternative 3 " + str(end - start))

这是n = {10000,100000,1000000}的结果：

n       alternative_1  alternative_310000   30.72s         8.14s100000  162.43s        70.64s1000000 1473.57s       688.59s

从结果来看，备选方案3比备选方案1更快。

BigQuery 和与 Google 表格的连接失败

QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(
            "SELECT commit,author,repo_name "
                + "FROM `bigquery-public-data.github_repos.commits` "
                + "WHERE subject like ''%bigquery%'' "
                + "ORDER BY subject DESC LIMIT 10")
        .setUseLegacysql(false)
        .build();

JobId jobId = JobId.of(UUID.randomUUID().toString());
Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

queryJob = queryJob.waitFor();
TableResult result = queryJob.getQueryResults();