CSV is a popularly used data format for storing structured or unstructured data in a tabular format or within a plain text file separated by commas. It is used by businesses to facilitate data exchange and storage, as CSV offers a lightweight and human-readable way to represent data. CSV is commonly employed for tasks such as data import or export, backups, sharing between different applications, and as a medium format for analysis. With benefits, it also has some limitations, like a lack of support for data types, relationships between data, and nested data. However, by moving data from CSV to BigQuery, you can address these limitations and unlock a multitude of benefits.

Let’s learn how!

What is BigQuery?

Developed by Google, BigQuery is a fully managed and serverless enterprise-ready data warehouse and analytics platform. With BigQuery, you can store, analyze, and gain valuable insights from your data. This makes it a versatile tool for data-driven organizations.

By moving data from CSV to BigQuery, you can streamline your data management and analysis processes. The other advantages include:

• BigQuery is a highly scalable and distributed data warehouse. This makes it suitable to handle enormous amounts of data. 

• The serverless architecture of BigQuery ensures that you don’t need to worry about managing infrastructure. This allows you to completely focus on data analysis.

• BigQuery supports standard SQL Queries. If your team is familiar with SQL, you can quickly use it for complex queries and reporting. 

• Bigquery seamlessly integrates with other Google Cloud services. This includes Cloud Storage, Dataflow, and Data Studio. This enables you to create an ecosystem that simplifies data management.

Why use Airbyte to Upload CSV Data to BigQuery?

Some key features of Airbyte include:

• Airbyte provides an intuitive interface for importing data, allowing you to interactively configure load options and monitor the process. Get started with Airbyte for free.
• It offers a growing library of 350+ extensive source and destination connectors. 
• Based on configuration settings, Airbyte can automatically discover and infer the source schema changes.
• Using Airbyte, you can establish a Change Data Capture (CDC) pipeline based on logs without the requirement to parse logs manually. 
• Airbyte allows users to apply transformations using a web-based interface, making it accessible to non-technical users.
• It supports incremental data synchronization, enabling efficient data replication between various systems.
• Being open-source, Airbyte has an active community of developers who contribute to its development and offer support through forums.

Replicate Data from CSV to BigQuery Using Airbyte

Airbyte is an open-source data integration platform. It is designed to facilitate the collection and movement of data from various sources to different destinations. Its destinations include popular databases, data warehouses, cloud services, APIs, and more. 

Apart from a wide range of connectors, what makes Airbyte notable is its support for real-time data synchronization. This feature allows you to quickly make decisions based on the most up-to-date data.

Let’s understand how to create a CSV to BigQuery ETL pipeline using Airbyte UI.

Prerequisites

• A CSV file hosted on AWS S3, GCS, HTTPS, or an SFTP server.
• GCP project in Google Cloud Console.

You would need a BigQuery dataset in your GCP project to load CSV file data. To create a dataset and a table within a dataset, follow the steps mentioned below:

• Log into Google Cloud Console > Create a project > Open BigQuery.
• In the BigQuery, select the project where you want to create the dataset.
• Click on the Create dataset button and provide a unique Dataset ID, Dataset location, description, and set access control for the dataset.
• After configuring the dataset details, click Create Dataset to create a new dataset.

• Open the dataset that you created and click on the Create Table button to create a new table where you’ll be storing CSV file data.

Step 1: Configure a CSV Source in Airbyte

• Sign up for Airbyte Cloud or deploy Airbyte OSS. After successful login, select Sources present on the Airbyte dashboard.
• In the Search bar, type CSV and select the File (CSV, JSON, Excel, Feather, Parquet) connector.

• In the Create a source section:
• Enter a Source name of your choice. 
• In the Dataset Name field, enter the name of the target table where you want to replicate the CSV file. 
• Select the File Format as CSV from the drop-down. 
• Choose the Storage Provider from the drop-down and configure the provider-specific fields. 
• Enter the URL path of the CSV file to be replicated in the URL field.

• Once you’ve filled in all the configuration fields, click on Set up source. 
• Now, Airbyte will start testing your connection. If all the fields are entered correctly, you’ll receive a connection successful message.
• For a comprehensive understanding of each field, you can refer to the Airbyte File source connector.

Step 2: Configure a BigQuery Destination in Airbyte 

• To set BigQuery as your destination in Airbyte, go back to the dashboard and select Destinations.
• Search BigQuery connector in the Search bar and select the connector.
• Enter the name for the BigQuery connector in the Destination name field. In the Connection section, enter your Google Cloud Project ID, Dataset location, Default Dataset ID, Location Method, Service Account Key JSON, GCS Bucket Name, and GCS Bucket Path.

• Click on the Set up Destination button, and Airbyte will initiate to test your destination connection.
• If you find any of the configuration fields unclear, you can refer to Airbyte’s BigQuery setup guide for clarification.

Step 3: Create an Airbyte Connection 

• Now go to the dashboard and select Connections from the left-side pane to establish a connection between CSV and BigQuery. Click on Create a new Connection.
• Next, choose the CSV File source you’ve recently created and repeat the same process for the BigQuery destination. 
• Provide the new Connection Name and set the Replication frequency. You can change the frequency depending on your requirements.
• On the same page, you can select the Sync mode for your source files. If you aren’t sure about which replication or sync mode to choose, refer to the replication modes in Airbyte. The image below illustrates the configuration steps within Airbyte.

Step 4: Review Data in BigQuery

Once you start the first sync, your data from CSV will be replicated to BigQuery. After a successful sync, you should be able to see your CSV file data migrated in the BigQuery tables.

This approach provides a user-friendly way to ingest and work with CSV data in BigQuery in real time.

Manual Approach to Add CSV Data to BigQuery

To load CSV data from Cloud Storage into a new BigQuery table, you have multiple options available, including: 

• Using the BigQuery Console,
• bq command line tool,
• Google Cloud’s API,
• Programming languages like Python, Ruby, Java, C#, Go, Java, Node.js, PHP, and Ruby.

Using the BigQuery Console

• To begin with CSV to BigQuery data integration using BigQuery console, sign in to your Google Cloud account. 
• If you haven’t already created a dataset in BigQuery, you can create one within your BigQuery project.
• Once your dataset is ready, select it, and then create a new table within the dataset to copy CSV file data.
• In the Create Table panel, choose to create a table from an uploaded CSV file. You can either upload the CSV file from your local machine or specify the Cloud Storage file path. Define the table schema and configure other settings like partitioning and clustering.

Using the bq command-line Tool

You can manually migrate data from CSV to BigQuery using the bq load command line tool. This assumes you have the Google Cloud SDK (gcloud) installed and properly configured.

First, ensure your CSV file is prepared with the data you want to load into BigQuery, including a header row with column names. You can upload a CSV file to the GCS for simpler access. The other way is using the gsutil command to copy the file to a GCS bucket.

The syntax to copy CSV files stored in your local machine to the GCS bucket is as follows:

gsutil cp path/file_name.csv gs://bucket_name/

Next, use the bq load command to load your CSV data into the BigQuery table. Specify the target dataset and table, as well as the GCS location of your file. You can also set additional options, such as defining the schema or delimiters as needed.

bq load --source_format=CSV dataset_name.table_name gs://bucket_name/file_name.csv

Replace the required fields and execute the command. Monitor the load job’s status with the bq show command.

While the manual approach seems simple, it can be time-consuming and burdensome due to the need for meticulous configuration and the potential for human error. We recommend using a real-time and reliable solution like Airbyte.

Wrapping up

Moving data from a CSV file to a robust warehouse like Google BigQuery can streamline your decision-making journey, facilitating data analysis and informed choices.

This article taught you how to implement an ELT pipeline from CSV to BigQuery quickly. This streamlined approach simplifies the process of capturing and loading CSV data to BigQuery.

You can quickly participate in discussions on Airbyte’s community Slack channel, where you can engage with a thriving community of data professionals, share your insights, and collectively contribute to the success of diverse projects.