❖ BigQuery

If your data is available in a BigQuery database, you can directly connect to it in order to extract the data and metadata. Later, you can use the same connections to write the data back to a database.

This functionality is in Beta! Beta functionality may have bugs and may change in the future. Help us out by testing this functionality and letting us know if you encounter any issues.

Installation

❖ SDV Enterprise Bundle. This feature is available as part of the AI Connectors Bundle, an optional add-on to SDV Enterprise. For more information, please visit the AI Connectors Bundle page.

To use this feature, please make sure you have installed the bundle with the optional db-bigquery dependency. For more information, see the SDV Enterprise Installation guide.

pip install -U bundle-ai-connectors[db-bigquery] --index-url https://pypi.datacebo.com

Use this connector to create a connection to your Google BigQuery database.

from sdv.io.database import BigQueryConnector

connector = BigQueryConnector()

Parameters (None)

Output A BigQueryConnector object that you can use to import data and metadata

Importing Real Data

Import your real data from a database to use with SDV.

❖ set_import_config

Use this function to authenticate into the project and database you'd like to import from.

connector.set_import_config(
    dataset_id='my_dataset',
    auth={
        ...
    }
)

Parameters

• (required) dataset_id: A string with the name of your dataset in BigQuery

• (required) auth: A dictionary with your authentication credentials (for details, see Authentication)

Output (None)

❖ create_metadata

Use this function to create metadata based on the connection to your database.

metadata = connector.create_metadata(
    table_names=['users', 'transactions', 'sessions'])

Parameters

• table_names: A list of strings representing the table names that you want to create metadata for

  • (default) None: Create metadata for all the tables in the database

• infer_sdtypes: A boolean describing whether to infer the sdtypes of each column

  • (default) True: Infer the sdtypes based on the data

  • False: Do not infer the sdtypes. All columns will be marked as unknown, ready for you to manually update

• infer_keys: A string describing whether to infer the primary and/or foreign keys. Options are:

  • (default) 'primary_and_foreign': Infer the primary keys in each table, and the foreign keys in other tables that refer to them

  • 'primary_only': Infer only the primary keys of each table

  • None: Do not infer any keys

Output A Metadata object representing your metadata

The detected metadata is not guaranteed to be accurate or complete. Be sure to carefully inspect the metadata and update information. For more information, see the Metadata API.

❖ import_random_subset

Use this function to import a random subset of your data from the database and inferred metadata. The size of the subset is automatically determined.

data = connector.import_random_subset(
    metadata=metadata,
    verbose=True
)

Parameters

• (required) metadata: A Metdata object that describes the data you want to import

• fixed_seed: A boolean that controls the determinism of the random subset

  • (default) False: Different random data will be imported every time you call the function

  • True: The same data will be imported every time you import

• verbose: A boolean describing whether to print out details about the progress of the import

  • (default) True: Print out the table names and number of rows being imported

  • False: Do not print any details

Output A dictionary that maps each table name of your database (string) to the data, represented as a pandas DataFrame.

❖ import_optimized_subset

Use this function to import a subset of your data, optimized specifically for a given table. You can also control the size.

data = connector.import_optimized_subset(
    metadata=metadata, 
    main_table_name='users',
    num_rows=5000
)

Parameters

• (required) metadata: A Metadata object that describes the data you want to import

• (required) main_table_name: A string containing the name of the most important table of your database. This table will generally represent the entity that is most critical to your application or business. It must be one of the tables listed in your metadata object.

• num_rows: The number of rows to sample from the main table. The size of every other table is automatically determined by its connection to the main table.

• fixed_seed: A boolean that controls the determinism of the random subset

  • (default) False: Different random data will be imported every time you call the function

  • True: The same data will be imported every time you import

• verbose: A boolean describing whether to print out details about the progress of the import

  • (default) True: Print out the table names and number of rows being imported

  • False: Do not print any details

Output A dictionary that maps each table name of your database (string) to the data, represented as a pandas DataFrame.

After importing the data and metadata, you are now ready to create an SDV synthesizer.

Exporting synthetic data

Export synthetic data into a new database.

We recommend using the same connector as your import. This connector object already knows about the specifics of your database schema. It will ensure that the exported data schema has the same format.

❖ set_export_config

Use this function to specify which project and database you'd like to export data to. Also provide your authentication credentials.

connector.set_export_config(
    dataset_id='my_dataset',
    auth={
        ...
    }
)

Parameters

• (required) dataset_id: A string with the name of your dataset in BigQuery

• (required) auth: A dictionary with your authentication credentials (for details, see Authentication)

Output (None)

❖ export

Use this function to export your synthetic data into a database

connector.export(
    data=synthetic_data,
    mode='write',
    verbose=True)

Parameters

• (required) synthetic_data: A dictionary that maps each table name to the synthetic data, represented as a pandas DataFrame

• mode: The mode of writing to use during the export

  • (default) 'write': Write a new database from scratch. If the database or data already exists, then the function will error out.

  • 'append': Append rows to existing tables in the database

  • 'overwrite': Remove any existing tables in the database and replace them with this data

• verbose: A boolean describing whether to print out details about export

  • (default) True: Print the details

  • False: Do not print anything

Output (None) Your data will be written to the database and ready for use by your downstream application!

FAQs

Authentication & Credentials

Before using your connector, you will need to authenticate into the database. To do this, start by determining whether your account is a user account or a service account. Service accounts are typically made for non-human users. (For more details, see the docs.)

Authenticating a user account

Prerequisites: If you are connecting a user account, we recommend using the Application Default Credentials (ADC). To get started, install the Google CLI using these instructions. Then run the following command on your terminal:

gcloud init
gcloud auth application-default login

This will prompt you to sign in, and then save the credentials onto your local machine in a pre-determined location (see the docs).

Instructions: Now, the AI connector will automatically be able to find your file and authenticate you. You can just provide the other information needed to identify your database.

connector.set_import_config(
    dataset_id='my_dataset',
    auth=None
)

Authenticating a service account

Prerequisites: If you are connecting a service account, start by downloading a JSON credential file for the service account onto your machine. Follow the instructions in these docs to select your service account, add a key, and download the JSON file. (If you need help with this step, please contact your database admin.) Then follow the recommended approach below.

Instructions (recommended approach): When authenticating your AI connector, pass in the JSON filepath along with other necessary info, as shown below.

connector.set_import_config(
    dataset_id='my_dataset',
    auth={
        'json_credentials_path': 'my_folder/credentials.json', # pass in JSON filepath
        'project_id': 'my_project_id' # required
    }
)

Instructions (alternative approach): Alternatively, you can save the JSON filepath and project ID as global variables by typing the terminal commands below.

export GOOGLE_APPLICATION_CREDENTIALS=my_folder/credentials.json
export GOOGLE_CLOUD_PROJECT=my_project_id

Once you've run the above lines of code, your AI Connector can automatically locate the JSON file that just got saved and pull out the auth credentials from it. It can also look up the project ID. So you don't have to specify this information anymore.

connector.set_import_config(
    dataset_id='my_dataset',
    auth=None
)

Other FAQs

Which permissions are needed for importing and exporting?

Importing data requires read access. For BigQuery, this includes: bigquery.jobs.create, bigquery.tables.get, bigquery.tables.getData, and bigquery.tables.list, bigquery.datasets.get. If you do not have these permissions, please contact your database admin.

Exporting data requires write access. For BigQuery, this includes: bigquery.datasets.create, bigquery.datasets.get, bigquery.jobs.create, bigquery.tables.create, and bigquery.tables.export. If you do not have these permissions, please contact your database admin.

Is there a way to speed up import and export?

Installing google-cloud-bigquery-storage in your environment enables the BigQuery Storage API for faster data import/export. This dependency is not currently required so be sure to install it yourself using your preferred package manager.

pip install google-cloud-bigquery-storage

To see results, make sure you have the bigquery.readsessions.create permission.