How to Upload a Local File and Create a Table in BigQuery?

In this post, I am going to explain how to upload a local file emps.csv and create a table in BigQuery.

emps.csv 

id,name,age,salary,is_active,location,joining_date,last_login
1,Ram,30,75000.50,TRUE,Hyderabad,2019-06-12,2024-03-10 14:30:00
2,Chamu,27,62000.00,FALSE,Bengaluru,2021-01-20,2024-03-11 08:15:45
3,Sailu,35,85000.75,TRUE,Chennai,2018-11-05,2024-03-12 19:05:30
4,Hari,40,95000.25,TRUE,Pune,2016-09-25,2024-03-13 11:22:10
5,Krishna,28,67000.00,FALSE,Mumbai,2020-03-18,2024-03-14 09:45:00
6,Gopi,33,88000.30,TRUE,Kolkata,2017-07-10,2024-03-15 12:10:30
7,Anil,29,72000.50,TRUE,Delhi,2019-12-22,2024-03-16 16:30:45
8,Sindhu,31,81000.20,FALSE,Jaipur,2022-05-14,2024-03-17 10:05:15
9,Meena,26,56000.40,TRUE,Ahmedabad,2023-01-08,2024-03-18 13:20:25
10,Veeru,34,94000.90,TRUE,Visakhapatnam,2015-08-30,2024-03-19 15:45:50

Step 1: Log in to Google Cloud Console

Open a web browser and navigate to the Google Cloud Console (https://console.cloud.google.com).

Enter your credentials to sign in to your Google Cloud account. Ensure you have the necessary permissions to create a dataset in BigQuery.

 

Step 2: Access BigQuery Studio

Once logged in, locate the Navigation Menu (☰) in the upper-left corner of the Cloud Console. Click on the menu to expand the options and scroll down to find BigQuery section.

 

Click on BigQuery -> Studio, to open the BigQuery workspace.

 

Step 3: Select the dataset.

 

In the previous post, we created the dataset test_dataset, click on it. 

Once you click on the dataset, the dataset information page opens.

 

Step 4: Click the CREATE TABLE button on the dataset information page to create a new table.

It opens Create table form, looks like below.

 

BigQuery allows you to create tables using various methods, depending on your data source. Here’s a brief explanation of each option:

 

·      Empty Table: Create a blank table by manually defining the schema and data types. This is useful when you plan to insert or stream data later.

·      Google Cloud Storage: Load data from CSV, JSON, Avro, ORC, or Parquet files stored in Google Cloud Storage. This method is efficient for handling large datasets.

·      Upload: Directly upload a local file (CSV, JSON, or Avro) from your computer. Suitable for small datasets that don’t require cloud storage.

·      Drive: Import data from Google Sheets or CSV files stored in Google Drive. Ideal for collaborative and frequently updated datasets.

·      Google Bigtable: Create a table by linking it to an existing Google Bigtable instance, useful for working with high-volume NoSQL data.

·      Amazon S3: Load data stored in Amazon S3 using BigQuery’s cross-cloud transfer service. This is helpful for migrating AWS datasets to BigQuery.

·      Azure Blob Storage: Import data from Azure Blob Storage, allowing seamless integration between Microsoft Azure and Google Cloud.

·      Existing Table/View: Create a new table by copying or referencing an existing table or view within BigQuery. Useful for managing subsets or transformed versions of existing data.

Select the "Upload" option.

 

After choosing "Upload," the BROWSE button becomes active, allowing you to select a file from your local system. Upload the emps.csv file, Choose CSV as the file format. Other supported formats include JSONL, Avro, Parquet, and ORC. 

Keep the project, dataset, and table type unchanged, and enter "emps" as the table name in the Destination section.  

BigQuery can automatically detect the schema from the data, but for this demo, we will define it manually. Click on ADD FIELD and specify each column name along with its corresponding data type.

 

Define the schema like below

Column Name	BigQuery Data Type
id	INTEGER
name	STRING
age	INTEGER
salary	FLOAT
is_active	BOOLEAN
location	STRING
joining_date	DATE
last_login	TIMESTAMP

 

 

Leave Partition, Tags, unchanged for simplicity.

 

In the Advanced Options section, set Header rows to skip to 1.

 

Click the "CREATE TABLE" button to create the emps table.

 

If everything is set up correctly, the emps table will be created under the test_dataset dataset. 

 

Click on emps table, you can see the SCHEMA details here

 

Click on PREVIEW tab to preview the data.

 

Click the QUERY button to open the query editor. Enter the following SQL statement and click RUN.

 

SELECT * FROM `test_dataset.emps` LIMIT 3;

 

Once the query executes successfully, the results will be displayed in the Query results section below.

 

That’s it for the demo. Happy learning…

 

Previous                                                    Next                                                    Home