Google BigQuery

    • Learn how to create a BigQuery dataset using Google Apps Script.

    • Understand the steps involved in creating a BigQuery data table with a defined schema.

    • Discover how to import data rows into a BigQuery table using a structured request.

    • See how to execute a query against a BigQuery table and retrieve the results.

    Create a BigQuery data set

     function 
  
 createDataSet 
 () 
  
 { 
  
 // 
  
 Replace 
  
 this 
  
 value 
  
 with 
  
 the 
  
 project 
  
 ID 
  
 listed 
  
 in 
  
 the 
  
 Google 
  
 // 
  
 Cloud 
  
 Platform 
  
 project 
 . 
  
 var 
  
 projectId 
  
 = 
  
 'INSERT_PROJECT_ID_HERE' 
 ; 
  
 var 
  
 dataSetId 
  
 = 
  
 'INSERT_DATASET_ID_HERE' 
 ; 
  
 var 
  
 dataSet 
  
 = 
  
 BigQuery 
 . 
 newDataset 
 (); 
  
 dataSet 
 . 
 id 
  
 = 
  
 dataSetId 
 ; 
  
 dataSet 
 . 
 friendlyName 
  
 = 
  
 'Fruit prices' 
 ; 
  
 dataSet 
 . 
 datasetReference 
  
 = 
  
 BigQuery 
 . 
 newDatasetReference 
 (); 
  
 dataSet 
 . 
 datasetReference 
 . 
 projectId 
  
 = 
  
 projectId 
 ; 
  
 dataSet 
 . 
 datasetReference 
 . 
 datasetId 
  
 = 
  
 dataSetId 
 ; 
  
 dataSet 
  
 = 
  
 BigQuery 
 . 
 Datasets 
 . 
 insert 
 ( 
 dataSet 
 , 
  
 projectId 
 ); 
  
 console 
 . 
 log 
 ( 
 'Data set with ID = 
 %s 
 , Name = 
 %s 
 created.' 
 , 
  
 dataSet 
 . 
 id 
 , 
  
 dataSet 
 . 
 friendlyName 
 ); 
 }

    Create a BigQuery data table

     function 
  
 createTable 
 () 
  
 { 
  
 // 
  
 Replace 
  
 this 
  
 value 
  
 with 
  
 the 
  
 project 
  
 ID 
  
 listed 
  
 in 
  
 the 
  
 Google 
  
 // 
  
 Cloud 
  
 Platform 
  
 project 
 . 
  
 var 
  
 projectId 
  
 = 
  
 'INSERT_PROJECT_ID_HERE' 
 ; 
  
 var 
  
 dataSetId 
  
 = 
  
 'INSERT_DATASET_ID_HERE' 
 ; 
  
 var 
  
 tableId 
  
 = 
  
 'INSERT_TABLE_ID_HERE' 
 ; 
  
 var 
  
 table 
  
 = 
  
 BigQuery 
 . 
 newTable 
 (); 
  
 var 
  
 schema 
  
 = 
  
 BigQuery 
 . 
 newTableSchema 
 (); 
  
 var 
  
 nameFieldSchema 
  
 = 
  
 BigQuery 
 . 
 newTableFieldSchema 
 (); 
  
 nameFieldSchema 
 . 
 description 
  
 = 
  
 'Name' 
 ; 
  
 nameFieldSchema 
 . 
 name 
  
 = 
  
 'Name' 
 ; 
  
 nameFieldSchema 
 . 
 type 
  
 = 
  
 'STRING' 
 ; 
  
 var 
  
 ageFieldSchema 
  
 = 
  
 BigQuery 
 . 
 newTableFieldSchema 
 (); 
  
 ageFieldSchema 
 . 
 description 
  
 = 
  
 'Price' 
 ; 
  
 ageFieldSchema 
 . 
 name 
  
 = 
  
 'Price' 
 ; 
  
 ageFieldSchema 
 . 
 type 
  
 = 
  
 'FLOAT' 
 ; 
  
 schema 
 . 
 fields 
  
 = 
  
 [ 
  
 nameFieldSchema 
 , 
  
 ageFieldSchema 
  
 ]; 
  
 table 
 . 
 schema 
  
 = 
  
 schema 
 ; 
  
 table 
 . 
 id 
  
 = 
  
 tableId 
 ; 
  
 table 
 . 
 friendlyName 
  
 = 
  
 'Fruit prices' 
 ; 
  
 table 
 . 
 tableReference 
  
 = 
  
 BigQuery 
 . 
 newTableReference 
 (); 
  
 table 
 . 
 tableReference 
 . 
 datasetId 
  
 = 
  
 dataSetId 
 ; 
  
 table 
 . 
 tableReference 
 . 
 projectId 
  
 = 
  
 projectId 
 ; 
  
 table 
 . 
 tableReference 
 . 
 tableId 
  
 = 
  
 tableId 
 ; 
  
 table 
  
 = 
  
 BigQuery 
 . 
 Tables 
 . 
 insert 
 ( 
 table 
 , 
  
 projectId 
 , 
  
 dataSetId 
 ); 
  
 console 
 . 
 log 
 ( 
 'Data table with ID = 
 %s 
 , Name = 
 %s 
 created.' 
 , 
  
 table 
 . 
 id 
 , 
  
 table 
 . 
 friendlyName 
 ); 
 }

    Import into BigQuery data table

     function 
  
 importData 
 () 
  
 { 
  
 // 
  
 Replace 
  
 this 
  
 value 
  
 with 
  
 the 
  
 project 
  
 ID 
  
 listed 
  
 in 
  
 the 
  
 Google 
  
 // 
  
 Cloud 
  
 Platform 
  
 project 
 . 
  
 var 
  
 projectId 
  
 = 
  
 'INSERT_PROJECT_ID_HERE' 
 ; 
  
 var 
  
 dataSetId 
  
 = 
  
 'INSERT_DATASET_ID_HERE' 
 ; 
  
 var 
  
 tableId 
  
 = 
  
 'INSERT_TABLE_ID_HERE' 
 ; 
  
 var 
  
 insertAllRequest 
  
 = 
  
 BigQuery 
 . 
 newTableDataInsertAllRequest 
 (); 
  
 insertAllRequest 
 . 
 rows 
  
 = 
  
 [] 
 ; 
  
 var 
  
 row1 
  
 = 
  
 BigQuery 
 . 
 newTableDataInsertAllRequestRows 
 (); 
  
 row1 
 . 
 insertId 
  
 = 
  
 1 
 ; 
  
 row1 
 . 
 json 
  
 = 
  
 { 
  
 'Name' 
 : 
  
 'Orange' 
 , 
  
 'Price' 
 : 
  
 3.34 
  
 } 
 ; 
  
 insertAllRequest 
 . 
 rows 
 . 
 push 
 ( 
 row1 
 ); 
  
 var 
  
 row2 
  
 = 
  
 BigQuery 
 . 
 newTableDataInsertAllRequestRows 
 (); 
  
 row2 
 . 
 insertId 
  
 = 
  
 2 
 ; 
  
 row2 
 . 
 json 
  
 = 
  
 { 
  
 'Name' 
 : 
  
 'Grape' 
 , 
  
 'Price' 
 : 
  
 5.48 
  
 } 
 ; 
  
 insertAllRequest 
 . 
 rows 
 . 
 push 
 ( 
 row2 
 ); 
  
 var 
  
 row3 
  
 = 
  
 BigQuery 
 . 
 newTableDataInsertAllRequestRows 
 (); 
  
 row3 
 . 
 insertId 
  
 = 
  
 3 
 ; 
  
 row3 
 . 
 json 
  
 = 
  
 { 
  
 'Name' 
 : 
  
 'Apple' 
 , 
  
 'Price' 
 : 
  
 2.50 
  
 } 
 ; 
  
 insertAllRequest 
 . 
 rows 
 . 
 push 
 ( 
 row3 
 ); 
  
 var 
  
 result 
  
 = 
  
 BigQuery 
 . 
 Tabledata 
 . 
 insertAll 
 ( 
 insertAllRequest 
 , 
  
 projectId 
 , 
  
 dataSetId 
 , 
  
 tableId 
 ); 
  
 if 
  
 ( 
 result 
 . 
 insertErrors 
  
 != 
  
 null 
 ) 
  
 { 
  
 var 
  
 allErrors 
  
 = 
  
 [] 
 ; 
  
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
 < 
 result 
 . 
 insertErrors 
 . 
 length 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 var 
  
 insertError 
  
 = 
  
 result 
 . 
 insertErrors 
 [ 
 i 
 ] 
 ; 
  
 allErrors 
 . 
 push 
 ( 
 Utilities 
 . 
 formatString 
 ( 
 'Error inserting item: %s' 
 , 
  
 insertError 
 . 
 index 
 )); 
  
 for 
  
 ( 
 var 
  
 j 
  
 = 
  
 0 
 ; 
  
 j 
 < 
 insertError 
 . 
 errors 
 . 
 length 
 ; 
  
 j 
 ++ 
 ) 
  
 { 
  
 var 
  
 error 
  
 = 
  
 insertError 
 . 
 errors 
 [ 
 j 
 ] 
 ; 
  
 allErrors 
 . 
 push 
 ( 
 Utilities 
 . 
 formatString 
 ( 
 '- ' 
  
 + 
  
 error 
 )); 
  
 } 
  
 } 
  
 console 
 . 
 log 
 ( 
 allErrors 
 . 
 join 
 ( 
 '\n' 
 )); 
  
 } 
  
 else 
  
 { 
  
 console 
 . 
 log 
 ( 
 Utilities 
 . 
 formatString 
 ( 
 '%s data rows inserted successfully.' 
 , 
  
 insertAllRequest 
 . 
 rows 
 . 
 length 
 )); 
  
 } 
 }

    Run query against BigQuery data table

     function 
  
 queryDataTable 
 () 
  
 { 
  
 // 
  
 Replace 
  
 this 
  
 value 
  
 with 
  
 the 
  
 project 
  
 ID 
  
 listed 
  
 in 
  
 the 
  
 Google 
  
 // 
  
 Cloud 
  
 Platform 
  
 project 
 . 
  
 var 
  
 projectId 
  
 = 
  
 'INSERT_PROJECT_ID_HERE' 
 ; 
  
 var 
  
 dataSetId 
  
 = 
  
 'INSERT_DATASET_ID_HERE' 
 ; 
  
 var 
  
 tableId 
  
 = 
  
 'INSERT_TABLE_ID_HERE' 
 ; 
  
 var 
  
 fullTableName 
  
 = 
  
 projectId 
  
 + 
  
 ':' 
  
 + 
  
 dataSetId 
  
 + 
  
 '.' 
  
 + 
  
 tableId 
 ; 
  
 var 
  
 queryRequest 
  
 = 
  
 BigQuery 
 . 
 newQueryRequest 
 (); 
  
 queryRequest 
 . 
 query 
  
 = 
  
 'select Name, Price from [' 
  
 + 
  
 fullTableName 
  
 + 
  
 '];' 
 ; 
  
 var 
  
 query 
  
 = 
  
 BigQuery 
 . 
 Jobs 
 . 
 query 
 ( 
 queryRequest 
 , 
  
 projectId 
 ); 
  
 if 
  
 ( 
 query 
 . 
 jobComplete 
 ) 
  
 { 
  
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
 < 
 query 
 . 
 rows 
 . 
 length 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 var 
  
 row 
  
 = 
  
 query 
 . 
 rows 
 [ 
 i 
 ] 
 ; 
  
 var 
  
 values 
  
 = 
  
 [] 
 ; 
  
 for 
  
 ( 
 var 
  
 j 
  
 = 
  
 0 
 ; 
  
 j 
 < 
 row 
 . 
 f 
 . 
 length 
 ; 
  
 j 
 ++ 
 ) 
  
 { 
  
 values 
 . 
 push 
 ( 
 row 
 . 
 f 
 [ 
 j 
 ] 
 . 
 v 
 ); 
  
 } 
  
 console 
 . 
 log 
 ( 
 values 
 . 
 join 
 ( 
 ',' 
 )); 
  
 } 
  
 } 
 }
    Design a Mobile Site
    View Site in Mobile | Classic
    Share by: