Loading Parquet data from Cloud Storage

Console

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the left pane, click explore Explorer .
3. In the Explorer pane, expand your project, click Datasets , and then select a dataset.
4. In the Dataset info section, click add_box Create table .
5. In the Create table pane, specify the following details:
1. In the Source section, select Google Cloud Storage in the Create table from list. Then, do the following:
   1. Select a file from the Cloud Storage bucket, or enter the Cloud Storage URI . You cannot include multiple URIs in the Google Cloud console, but wildcards are supported. The Cloud Storage bucket must be in the same location as the dataset that contains the table you want to create, append, or overwrite.
   2. For File format , select Parquet .
2. In the Destination section, specify the following details:
   1. For Dataset , select the dataset in which you want to create the table.
   2. In the Table field, enter the name of the table that you want to create.
   3. Verify that the Table type field is set to Native table .
3. In the Schema section, no action is necessary. The schema is self-described in Parquet files.
4. Optional: Specify Partition and cluster settings . For more information, see Creating partitioned tables and Creating and using clustered tables .
5. Click Advanced options and do the following:
   • For Write preference , leave Write if empty selected. This option creates a new table and loads your data into it.
   • If you want to ignore values in a row that are not present in the table's schema, then select Unknown values .
   • For Encryption , click Customer-managed key to use a Cloud Key Management Service key . If you leave the Google-managed key setting, BigQuery encrypts the data at rest .
6. Click Create table .

SQL

Use the LOAD DATA DDL statement . The following example loads a Parquet file into the new table mytable :

1. In the Google Cloud console, go to the BigQuerypage.

   Go to BigQuery

2. In the query editor, enter the following statement:

    LOAD 
     
     DATA 
    
     
    OVERWRITE 
     
    mydataset 
    . 
    mytable 
    FROM 
     
    FILES 
     
    ( 
     
    format 
     
    = 
     
    'PARQUET' 
    , 
     
    uris 
     
    = 
     
    [ 
    'gs://bucket/path/file.parquet' 
    ] 
    ); 
   

3. Click play_circle Run.

For more information about how to run queries, see Run an interactive query .

bq

Use the bq load command, specify PARQUET using the --source_format flag, and include a Cloud Storage URI . You can include a single URI, a comma-separated list of URIs, or a URI containing a wildcard .

(Optional) Supply the --location flag and set the value to your location .

Other optional flags include:

• --time_partitioning_type : Enables time-based partitioning on a table and sets the partition type. Possible values are HOUR , DAY , MONTH , and YEAR . This flag is optional when you create a table partitioned on a DATE , DATETIME , or TIMESTAMP column. The default partition type for time-based partitioning is DAY . You cannot change the partitioning specification on an existing table.
• --time_partitioning_expiration : An integer that specifies (in seconds) when a time-based partition should be deleted. The expiration time evaluates to the partition's UTC date plus the integer value.
• --time_partitioning_field : The DATE or TIMESTAMP column used to create a partitioned table. If time-based partitioning is enabled without this value, an ingestion-time partitioned table is created.
• --require_partition_filter : When enabled, this option requires users to include a WHERE clause that specifies the partitions to query. Requiring a partition filter can reduce cost and improve performance. For more information, see Require a partition filter in queries .
• --clustering_fields : A comma-separated list of up to four column names used to create a clustered table .
• --destination_kms_key : The Cloud KMS key for encryption of the table data.

• --column_name_character_map : Defines the scope and handling of characters in column names, with the option of enabling flexible column names . For more information, see load_option_list . For more information on supported and unsupported characters, see flexible column names .

  For more information on partitioned tables, see:

  • Creating partitioned tables

  For more information on clustered tables, see:

  • Creating and using clustered tables

  For more information on table encryption, see:

  • Protecting data with Cloud KMS keys

To load Parquet data into BigQuery, enter the following command:

bq  
--location = 
 LOCATION 
  
load  
 \ 
--source_format = 
 FORMAT 
  
 \ 
 DATASET 
. TABLE 
  
 \ 
 PATH_TO_SOURCE 

Replace the following:

• LOCATION : your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1 . You can set a default value for the location using the .bigqueryrc file .
• FORMAT : PARQUET .
• DATASET : an existing dataset.
• TABLE : the name of the table into which you're loading data.
• PATH_TO_SOURCE : a fully qualified Cloud Storage URI or a comma-separated list of URIs. Wildcards are also supported.

Examples:

The following command loads data from gs://mybucket/mydata.parquet into a table named mytable in mydataset .

   
 bq 
  
 load 
  
\  
 -- 
 source_format 
 = 
 PARQUET 
  
\  
 mydataset 
 . 
 mytable 
  
\  
 gs 
 : 
 // 
 mybucket 
 / 
 mydata 
 . 
 parquet 
 

The following command loads data from gs://mybucket/mydata.parquet into a new ingestion-time partitioned table named mytable in mydataset .

   
 bq 
  
 load 
  
\  
 -- 
 source_format 
 = 
 PARQUET 
  
\  
 -- 
 time_partitioning_type 
 = 
 DAY 
  
\  
 mydataset 
 . 
 mytable 
  
\  
 gs 
 : 
 // 
 mybucket 
 / 
 mydata 
 . 
 parquet 
 

The following command loads data from gs://mybucket/mydata.parquet into a partitioned table named mytable in mydataset . The table is partitioned on the mytimestamp column.

   
 bq 
  
 load 
  
\  
 -- 
 source_format 
 = 
 PARQUET 
  
\  
 -- 
 time_partitioning_field 
  
 mytimestamp 
  
\  
 mydataset 
 . 
 mytable 
  
\  
 gs 
 : 
 // 
 mybucket 
 / 
 mydata 
 . 
 parquet 
 

The following command loads data from multiple files in gs://mybucket/ into a table named mytable in mydataset . The Cloud Storage URI uses a wildcard.

   
 bq 
  
 load 
  
\  
 -- 
 source_format 
 = 
 PARQUET 
  
\  
 mydataset 
 . 
 mytable 
  
\  
 gs 
 : 
 // 
 mybucket 
 / 
 mydata 
 *. 
 parquet 
 

The following command loads data from multiple files in gs://mybucket/ into a table named mytable in mydataset . The command includes a comma- separated list of Cloud Storage URIs with wildcards.

   
 bq 
  
 load 
  
\  
 -- 
 source_format 
 = 
 PARQUET 
  
\  
 mydataset 
 . 
 mytable 
  
\  
 "gs://mybucket/00/*.parquet" 
 , 
 "gs://mybucket/01/*.parquet" 
 

API

1. Create a load job that points to the source data in Cloud Storage.

2. (Optional) Specify your location in the location property in the jobReference section of the job resource .

3. The source URIs property must be fully qualified, in the format gs:// BUCKET / OBJECT . Each URI can contain one '*' wildcard character .

4. Specify the Parquet data format by setting the sourceFormat property to PARQUET .

5. To check the job status, call jobs.get( JOB_ID *) , replacing JOB_ID with the ID of the job returned by the initial request.

   • If status.state = DONE , the job completed successfully.
   • If the status.errorResult property is present, the request failed, and that object includes information describing what went wrong. When a request fails, no table is created and no data is loaded.
   • If status.errorResult is absent, the job finished successfully; although, there might have been some nonfatal errors, such as problems importing a few rows. Nonfatal errors are listed in the returned job object's status.errors property.

API notes:

• Load jobs are atomic and consistent: if a load job fails, none of the data is available, and if a load job succeeds, all of the data is available.

• As a best practice, generate a unique ID and pass it as jobReference.jobId when calling jobs.insert to create a load job. This approach is more robust to network failure because the client can poll or retry on the known job ID.

• Calling jobs.insert on a given job ID is idempotent. You can retry as many times as you like on the same job ID, and at most one of those operations will succeed.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Go API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

  import 
  
 ( 
  
 "context" 
  
 "fmt" 
  
 "cloud.google.com/go/bigquery" 
 ) 
 // importParquet demonstrates loading Apache Parquet data from Cloud Storage into a table. 
 func 
  
 importParquet 
 ( 
 projectID 
 , 
  
 datasetID 
 , 
  
 tableID 
  
 string 
 ) 
  
 error 
  
 { 
  
 // projectID := "my-project-id" 
  
 // datasetID := "mydataset" 
  
 // tableID := "mytable" 
  
 ctx 
  
 := 
  
 context 
 . 
 Background 
 () 
  
 client 
 , 
  
 err 
  
 := 
  
 bigquery 
 . 
 NewClient 
 ( 
 ctx 
 , 
  
 projectID 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "bigquery.NewClient: %v" 
 , 
  
 err 
 ) 
  
 } 
  
 defer 
  
 client 
 . 
 Close 
 () 
  
 gcsRef 
  
 := 
  
 bigquery 
 . 
  NewGCSReference 
 
 ( 
 "gs://cloud-samples-data/bigquery/us-states/us-states.parquet" 
 ) 
  
 gcsRef 
 . 
 SourceFormat 
  
 = 
  
 bigquery 
 . 
  Parquet 
 
  
 gcsRef 
 . 
 AutoDetect 
  
 = 
  
 true 
  
 loader 
  
 := 
  
 client 
 . 
 Dataset 
 ( 
 datasetID 
 ). 
 Table 
 ( 
 tableID 
 ). 
  LoaderFrom 
 
 ( 
 gcsRef 
 ) 
  
 job 
 , 
  
 err 
  
 := 
  
 loader 
 . 
 Run 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 status 
 , 
  
 err 
  
 := 
  
 job 
 . 
 Wait 
 ( 
 ctx 
 ) 
  
 if 
  
 err 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 err 
  
 } 
  
 if 
  
 status 
 . 
  Err 
 
 () 
  
 != 
  
 nil 
  
 { 
  
 return 
  
 fmt 
 . 
 Errorf 
 ( 
 "job completed with error: %v" 
 , 
  
 status 
 . 
  Err 
 
 ()) 
  
 } 
  
 return 
  
 nil 
 }