Loading Data with SQL DML

Google BigQuery supports loading data using the insert command from the SQL Data Manipulation Language (DML).

For large databases, load data using a file, instead. The number of INSERT statements that can be processed is limited to 1,000 per day per table. DML statements that modify partitioned tables are not yet supported.

Before you begin

To load data into BigQuery, you must set up billing and ensure that you have write access to the table.

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Projects page

  3. Enable billing for your project.

    Enable billing

  4. Ensure that you have write access to your table. If you are the owner of the dataset that contains your table, you probably have write access.

    To set write access to a dataset in BigQuery:

    1. Go to the BigQuery web UI.
      Go to the BigQuery web UI
    2. In the navigation, hover on a dataset ID and click the down arrow icon down arrow icon image next to the ID and click Share dataset.
    3. Add a person and give that person edit access, then click Save changes.

This how-to guide uses a MySQL database. If you want to try the steps here on a test database, you can generate one using the populate_db.py script in the sample code.

Creating a SQL file export

To export a MySQL database, use the mysqldump command.

Note that the mysqldump command supports exporting to CSV files using the --tab=dir_name, --fields-terminated-by=,, and --fields-enclosed-by='"' options. You can then load these CSV files directly into BigQuery rather than following this guide.

The following command exports the sample database into a SQL format that the sample code can send to BigQuery.

mysqldump \
    --user=root \
    --password='db-password' \
    --host=127.0.0.1 \
    --no-create-info \
    --complete-insert \
    sample_db \
    --skip-add-locks > sample_db_export.sql

The --no-create-info flag specifies not to add the statements to create the table. BigQuery doesn't support the SQL data definition language (DDL), so you need to create the schemas using the BigQuery UI or command-line.

The --complete-insert flag will include the column names in the insert statements. Use the same column names in BigQuery as were used in MySQL. The exported SQL file should look something like:

-- MySQL dump 10.13
...

/*!40000 ALTER TABLE `UserSessions` DISABLE KEYS */;
INSERT INTO `UserSesssions` (`id`, ...) VALUES (1,...,'2016-08-16 06:24:20',...),(2,...
...

Each INSERT statement appears on its own line.

Creating a BigQuery dataset

First, make a dataset to hold the tables.

Web UI

  1. Open the BigQuery web UI.
  2. Click the arrow next to your project name. Arrow next to project name
  3. Click Create new dataset.
  4. Enter a Dataset ID (Example: sample_db).
  5. Click OK.

Command-line

To use the bq command-line tool to create a dataset, use the bq mk command.

bq mk sample_db

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

public void CreateDataset(string datasetId, BigQueryClient client)
{
    var dataset = client.GetOrCreateDataset(datasetId);
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

if err := client.Dataset(datasetID).Create(ctx); err != nil {
	return err
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

Dataset dataset = null;
DatasetInfo datasetInfo = DatasetInfo.newBuilder(datasetName).build();
try {
  // the dataset was created
  dataset = bigquery.create(datasetInfo);
} catch (BigQueryException e) {
  // the dataset was not created
}

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

function createDataset (datasetId) {
  // Instantiates a client
  const bigquery = BigQuery();

  // Creates a new dataset, e.g. "my_new_dataset"
  return bigquery.createDataset(datasetId)
    .then((results) => {
      const dataset = results[0];
      console.log(`Dataset ${dataset.id} created.`);
      return dataset;
    });
}

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * @param string $projectId The Google project ID.
 * @param string $dataset   The BigQuery dataset ID.
 */
function create_dataset($projectId, $datasetId)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->createDataset($datasetId);
    return $dataset;
}

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset to create"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id

bigquery.create_dataset dataset_id

puts "Created dataset: #{dataset_id}"

Next, make tables within the dataset you just created.

Web UI

  1. Open the BigQuery web UI.
  2. Hover over your dataset ID.
  3. Click the arrow next to your dataset ID. Arrow next to dataset name
  4. Click Create new table.
  5. Select None (create empty table) for the Location.
  6. Choose the Table name to be identical to the name from your exported SQL database.

  7. Under Schema enter a Name for each column in the table and select the appropriate Type.

    • Click Add field and repeat until you have entered all the columns in your table.

  8. Click Create table.

The database created by the sample code has the following tables and columns:

  • Users
    • Name id, Type INTEGER
    • Name date_joined, Type TIMESTAMP
  • UserSessions
    • Name id, Type STRING
    • Name user_id, Type INTEGER
    • Name login_time, Type TIMESTAMP
    • Name logout_time, Type TIMESTAMP
    • Name ip_address, Type STRING

Command-line

To use the bq command-line tool to create an empty table, use the bq mk command.

To create the Users table for the database created by the sample code

bq mk --schema id:integer,date_joined:timestamp -t sample_db.Users

Create an empty UserSessions table with the command:

bq mk \
    --schema id:string,user_id:integer,login_time:timestamp,logout_time:timestamp,ip_address:string \
    -t sample_db.UserSessions

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

public void CreateTable(string datasetId, string tableId, BigQueryClient client)
{
    var dataset = client.GetDataset(datasetId);
    // Create schema for new table.
    var schema = new TableSchemaBuilder
    {
        { "title", BigQueryDbType.String },
        { "unique_words", BigQueryDbType.Int64 }
    }.Build();
    // Create the table if it doesn't exist.
    BigQueryTable table = dataset.GetOrCreateTable(tableId, schema);
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

// Item represents a row item.
type Item struct {
	Name  string
	Count int
}

// Save implements the ValueSaver interface.
func (i *Item) Save() (map[string]bigquery.Value, string, error) {
	return map[string]bigquery.Value{
		"Name":  i.Name,
		"Count": i.Count,
	}, "", nil
}

	schema, err := bigquery.InferSchema(Item{})
	if err != nil {
		return err
	}
	table := client.Dataset(datasetID).Table(tableID)
	if err := table.Create(ctx, schema); err != nil {
		return err
	}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

TableId tableId = TableId.of(datasetName, tableName);
// Table field definition
Field field = Field.of(fieldName, Field.Type.string());
// Table schema definition
Schema schema = Schema.of(field);
TableDefinition tableDefinition = StandardTableDefinition.of(schema);
TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
Table table = bigquery.create(tableInfo);

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

function createTable (datasetId, tableId, schema, projectId) {
  // Instantiates a client
  const bigquery = BigQuery({
    projectId: projectId
  });

  // References an existing dataset, e.g. "my_dataset"
  const dataset = bigquery.dataset(datasetId);

  // Specify a schema, e.g. "Name:string, Age:integer, Weight:float, IsMagic:boolean"
  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema
  };

  // Create a new table in the dataset
  return dataset.createTable(tableId, options)
    .then((results) => {
      const table = results[0];
      console.log(`Table ${table.id} created.`);
      return table;
    });
}

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Example:
 * ```
 * $fields = [
 *     [
 *         'name' => 'field1',
 *         'type' => 'string',
 *         'mode' => 'required'
 *     ],
 *     [
 *         'name' => 'field2',
 *         'type' => 'integer'
 *     ],
 * ];
 * $schema = ['fields' => $fields];
 * create_table($projectId, $datasetId, $tableId, $schema);
 * ```
 * @param string $projectId The Google project ID.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId   The BigQuery table ID.
 * @param array  $schema    The BigQuery table schema.
 */
function create_table($projectId, $datasetId, $tableId, $schema)
{
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $options = ['schema' => $schema];
    $table = $dataset->createTable($tableId, $options);
    return $table;
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

def create_table(dataset_name, table_name, project=None):
    """Creates a simple table in the given dataset.

    If no project is specified, then the currently active project is used.
    """
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)

    if not dataset.exists():
        print('Dataset {} does not exist.'.format(dataset_name))
        return

    table = dataset.table(table_name)

    # Set the table schema
    table.schema = (
        bigquery.SchemaField('Name', 'STRING'),
        bigquery.SchemaField('Age', 'INTEGER'),
        bigquery.SchemaField('Weight', 'FLOAT'),
    )

    table.create()

    print('Created table {} in dataset {}.'.format(table_name, dataset_name))

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

# project_id = "Your Google Cloud project ID"
# dataset_id = "ID of the dataset to create table in"
# table_id   = "ID of the table to create"

require "google/cloud/bigquery"

bigquery = Google::Cloud::Bigquery.new project: project_id
dataset  = bigquery.dataset dataset_id

dataset.create_table table_id

puts "Created table: #{table_id}"

Loading data using SQL DML

Now that you have an exported database, you can import the rows into BigQuery. To use DML in a query, you must enable standard SQL syntax. This sample runs a standard SQL query for each query line in a SQL file.

Web UI

The BigQuery web UI does not provide a way to load data from a SQL file directly, but you can run individual DML queries by enabling standard SQL in the query options.

See the DML reference page for details.

Command-line

To run a DML query using the bq command-line tool use the bq query command and specify --nouse_legacy_sql to use standard SQL syntax, which is required to use DML.

Since the INSERT queries may not contain the dataset name in the table identifier, use the --dataset_id parameter to specify the default target dataset.

The following Bash shell commands read all the lines beginning with INSERT and run the queries using the bq command-line tool.

while read -r q; do
    bq query --project_id=my-proj --dataset_id=sample_db --nouse_legacy_sql "$q"
done < <(grep '^INSERT' sample_db_export.sql)

These PowerShell commands also read lines beginning with INSERT and run the queries using the bq command-line tool.

Select-String -pattern '^INSERT' ./sample_db_export.sql |
    %{ bq query --project=my-proj --dataset_id=sample_db --nouse_legacy_sql $_.Line }

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * @param string $projectId The Google project ID.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $source    The path to the source file to import.
 */
function insert_sql($projectId, $datasetId, $source)
{
    // instantiate the bigquery client
    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);
    // run a sync query for each line of the import
    $file = fopen($source, 'r');
    while ($line = fgets($file)) {
        if (0 !== strpos(trim($line), 'INSERT')) {
            continue;
        }
        $bigQuery->runQuery($line, [
            'useLegacySql' => false,
            'defaultDataset' => ['datasetId' => $datasetId],
        ]);
    }
    print('Data imported successfully' . PHP_EOL);
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries. 

import argparse

from google.cloud import bigquery


def insert_sql(project, default_dataset, sql_path):
    """Run all the SQL statements in a SQL file."""

    client = bigquery.Client(project=project)

    with open(sql_path) as f:
        for line in f:
            line = line.strip()

            if not line.startswith('INSERT'):
                continue

            print('Running query: {}{}'.format(
                line[:60],
                '...' if len(line) > 60 else ''))
            query = client.run_sync_query(line)

            # Set use_legacy_sql to False to enable standard SQL syntax.
            # This is required to use the Data Manipulation Language features.
            #
            # For more information about enabling standard SQL, see:
            # https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql
            query.use_legacy_sql = False
            query.default_dataset = client.dataset(default_dataset)
            query.run()


if __name__ == "__main__":
        parser = argparse.ArgumentParser(
            description=__doc__,
            formatter_class=argparse.RawDescriptionHelpFormatter)
        parser.add_argument('project', help='Google Cloud project name')
        parser.add_argument(
            'default_dataset', help='Default BigQuery dataset name')
        parser.add_argument('sql_path', help='Path to SQL file')

        args = parser.parse_args()

        insert_sql(args.project, args.default_dataset, args.sql_path)

Next steps

Send feedback about...

This page
Documentation feedback
BigQuery Documentation
Product feedback