不使用Google Cloud Storage将BigQuery数据导出到CSV [英] Export BigQuery Data to CSV without using Google Cloud Storage

问题描述

我目前正在编写软件，以导出大量的BigQuery数据并将查询的结果作为CSV文件存储在本地.我使用了Python 3和Google提供的客户端.我进行了配置和身份验证，但问题是我无法在本地存储数据.每次执行时，都会显示以下错误消息:

I am currently writing a software, to export large amounts of BigQuery data and store the queried results locally as CSV files. I used Python 3 and the client provided by google. I did configuration and authentification, but the problem is, that i can't store the data locally. Everytime i execute, i get following error message:

googleapiclient.errors.HttpError:https://www.googleapis.com/bigquery/v2/projects/round-office-769/jobs?alt=json返回了无效的提取目标URI'响应/文件名-*.csv'.必须是有效的Google存储路径.>

googleapiclient.errors.HttpError: https://www.googleapis.com/bigquery/v2/projects/round-office-769/jobs?alt=json returned "Invalid extract destination URI 'response/file-name-*.csv'. Must be a valid Google Storage path.">

这是我的工作配置:

def export_table(service, cloud_storage_path,
             projectId, datasetId, tableId, sqlQuery,
             export_format="CSV",
             num_retries=5):

# Generate a unique job_id so retries
# don't accidentally duplicate export
job_data = {
    'jobReference': {
        'projectId': projectId,
        'jobId': str(uuid.uuid4())
    },
    'configuration': {
        'extract': {
            'sourceTable': {
                'projectId': projectId,
                'datasetId': datasetId,
                'tableId': tableId,
            },
            'destinationUris': ['response/file-name-*.csv'],
            'destinationFormat': export_format
        },
        'query': {
            'query': sqlQuery,
        }
    }
}
return service.jobs().insert(
    projectId=projectId,
    body=job_data).execute(num_retries=num_retries)

我希望我可以使用本地路径而不是云存储来存储数据，但是我错了.

I hoped i could just use a local path instead of a cloud storage, to store data, but i was wrong.

所以我的问题是:

我可以在本地(或本地数据库)下载查询的数据，还是必须使用Google Cloud Storage?

Can i download the queried data locally(or to a local database) or do i have to use Google Cloud Storage?

推荐答案

您需要使用Google Cloud Storage进行导出作业.在此处中说明了从BigQuery导出数据的操作，还请检查不同路径的变体语法.

You need to use Google Cloud Storage for your export job. Exporting data from BigQuery is explained here, check also the variants for different path syntaxes.

然后，您可以将文件从GCS下载到本地存储中.

Then you can download the files from GCS to your local storage.

Gsutil 工具可以帮助您进一步从GCS下载文件到本地计算机.

Gsutil tool can help you further to download the file from GCS to local machine.

您不能一口气下载到本地，首先需要导出到GCS，然后再转移到本地计算机.

You cannot download with one move locally, you first need to export to GCS, than to transfer to local machine.

这篇关于不使用Google Cloud Storage将BigQuery数据导出到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！