Connect to SQL database source data

Use the information provided here to connect to source data stored in SQL databases. Great Expectations (GX) uses SQLAlchemy to connect to SQL source data, and most of the SQL dialects supported by SQLAlchemy are also supported by GX. For more information about the SQL dialects supported by SQLAlchemy, see Dialects.

SQL

SQL

Connect GX to a SQL database to access source data.

Prerequisites

• An installation of GX set up to work with SQL
• Source data stored in a SQL database

Import GX and instantiate a Data Context

Run the following Python code to import GX and instantiate a Data Context:

import great_expectations as gx

context = gx.get_context()

Determine your connection string

GX supports numerous SQL source data systems. However, most SQL dialects have their own specifications for defining a connection string. See the dialect documentation to determine the connection string for your SQL database.

Some examples of different connection strings:

The following are some of the connection strings that are available for different SQL dialects:

• AWS Athena: awsathena+rest://@athena.<REGION>.amazonaws.com/<DATABASE>?s3_staging_dir=<S3_PATH>
• BigQuery: bigquery://<GCP_PROJECT>/<BIGQUERY_DATASET>?credentials_path=/path/to/your/credentials.json
• MSSQL: mssql+pyodbc://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>?driver=<DRIVER>&charset=utf&autocommit=true
• MySQL: mysql+pymysql://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>
• PostgreSQL: postgresql+psycopg2://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>
• Redshift: postgresql+psycopg2://<USER_NAME>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>?sslmode=<SSLMODE>
• Snowflake: snowflake://<USER_NAME>:<PASSWORD>@<ACCOUNT_NAME>/<DATABASE_NAME>/<SCHEMA_NAME>?warehouse=<WAREHOUSE_NAME>&role=<ROLE_NAME>&application=great_expectations_oss
• SQLite: sqlite:///<PATH_TO_DB_FILE>
• Trino: trino://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<CATALOG>/<SCHEMA>

Run one of the connection strings in your preferred SQL dialect to store the connection string in the connection_string variable with plain text credentials. The following code is an example of the PostgreSQL connection string format:

connection_string = "postgresql+psycopg2://username:my_password@localhost/test"

Is there a more secure way to include my credentials?

You can use environment variables or a key in config_variables.yml to store connection string passwords. After you define your password, you reference it in your connection string similar to this example:

connection_string = (
    "postgresql+psycopg2://<username>:${MY_PASSWORD}@<host>:<port>/<database>"
)

In the previous example MY_PASSWORD is the name of the environment variable, or the key to the value in config_variables.yml that corresponds to your password.

If you include a password as plain text in your connection string when you define your Data Source, GX automatically removes it, adds it to config_variables.yml, and substitutes it in the Data Source saved configuration with a variable.

Create a SQL Data Source

Run the following Python code to create a SQL Data Source:

datasource = context.sources.add_sql(
    name="my_datasource", connection_string=connection_string
)

PostgreSQL

PostgreSQL

Connect GX to a PostgreSQL database to access source data.

Prerequisites

• An installation of GX set up to work with PostgreSQL
• Source data stored in a PostgreSQL database

Import GX and instantiate a Data Context

Run the following Python code to import GX and instantiate a Data Context:

import great_expectations as gx

context = gx.get_context()

Determine your connection string

The following code examples use a PostgreSQL connection string. A PostgreSQL connection string connects GX to the PostgreSQL database.

The following code is an example of a PostgreSQL connection string format:

my_connection_string = (
    "postgresql+psycopg2://<username>:<password>@<host>:<port>/<database>"
)

Is there a more secure way to store my credentials than plain text in a connection string?

We recommend that database credentials be stored in the config_variables.yml file, which is located in the uncommitted/ folder by default, and is not part of source control. The following lines add database credentials under the key db_creds.

YAML file contents

db_creds:
  drivername: postgres
  host: '<your_host_name>'
  port: '<your_port>'
  username: '<your_username>'
  password: '<your_password>'
  database: '<your_database_name>'

For additional options on configuring the config_variables.yml file or additional environment variables, please see our guide on how to configure credentials.

Create a PostgreSQL Data Source

1. Run the following Python code to set the name and connection_string variables:

   datasource_name = "my_datasource"
   my_connection_string = (
       "postgresql+psycopg2://<username>:<password>@<host>:<port>/<database>"
   )

2. Run the following Python code to create a PostgreSQL Data Source:

   datasource = context.sources.add_postgres(
       name=datasource_name, connection_string=my_connection_string
   )

Connect to a specific set of data with a Data Asset

To connect the Data Source to a specific set of data in the database, you define a Data Asset in the Data Source. A Data Source can contain multiple Data Assets. Each Data Asset acts as the interface between GX and the specific set of data it is configured for.

With SQL databases, you can use Table or Query Data Assets. The Table Data Asset connects GX to the data contained in a single table in the source database. The Query Data Asset connects GX to the data returned by a SQL query.

Maximum allowable Data Assets for a Data Source

Although there isn't a maximum number of Data Assets you can define for a Data Source, you must create a single Data Asset to allow GX to retrieve data from your Data Source.

Connect a Data Asset to the data in a table (Optional)

1. Run the following Python code to identify the table to connect to with a Table Data Asset:

   asset_name = "my_table_asset"
   asset_table_name = "postgres_taxi_data"

2. Run the following Python code to create the Data Asset:

   table_asset = datasource.add_table_asset(name=asset_name, table_name=asset_table_name)

Connect a Data Asset to the data returned by a query (Optional)

1. Run the following Python code to define a Query Data Asset:

   asset_name = "my_query_asset"
   asset_query = "SELECT * from postgres_taxi_data"

2. Run the following Python code to create the Data Asset:

   query_asset = datasource.add_query_asset(name=asset_name, query=asset_query)

Connect to additional tables or queries (Optional)

Repeat the previous steps to add additional Data Assets.

SQLite

SQLite

Connect GX to a SQLite database to access source data.

Prerequisites

• An installation of GX set up to work with SQLite
• Source data stored in a SQLite database

Import GX and instantiate a Data Context

Run the following Python code to import GX and instantiate a Data Context:

import great_expectations as gx

context = gx.get_context()

Determine your connection string

The following code examples use a SQLite connection string. A SQLite connection string connects GX to the SQLite database.

The following code is an example of a SQLite connection string format:

my_connection_string = "sqlite:///<path_to_db_file>"

Create a SQLite Data Source

1. Run the following Python code to set the name and connection_string variables:

   datasource_name = "my_datasource"

2. Run the following Python code to create a SQLite Data Source:

   datasource = context.sources.add_sqlite(
       name=datasource_name, connection_string=my_connection_string
   )
   Using add_sql(...) instead of add_sqlite(...)

   The SQL Data Source created with add_sql can connect to data in a SQLite database. However, add_sqlite(...) is the preferred method.

   SQLite stores datetime values as strings. Because of this, a general SQL Data Source sees datetime columns as string columns. A SQLite Data Source has additional handling in place for these fields, and also has additional error reporting for SQLite specific issues.

   If you are working with SQLite source data, use add_sqlite(...) to create your Data Source.

Connect to the data in a table (Optional)

1. Run the following Python code to set the asset_name and asset_table_name variables:

   asset_name = "my_asset"
   asset_table_name = my_table_name

2. Run the following Python code to create the Data Asset:

   table_asset = datasource.add_table_asset(name=asset_name, table_name=asset_table_name)

Connect to the data in a query (Optional)

1. Run the following Python code to define a Query Data Asset:

   asset_name = "my_query_asset"
   query = "SELECT * from yellow_tripdata_sample_2019_01"

2. Run the following Python code to create the Data Asset:

   query_asset = datasource.add_query_asset(name=asset_name, query=query)

Add additional tables or queries (Optional)

Repeat the previous steps to add additional Data Assets.

Snowflake

Snowflake

Connect GX to a Snowflake database to access source data.

Prerequisites

• An installation of GX set up to work with SQL
• Source data stored in a Snowflake database

Import GX and instantiate a Data Context

Run the following Python code to import GX and instantiate a Data Context:

import great_expectations as gx

context = gx.get_context()

Determine your connection string

The following code examples use a Snowflake connection string. A Snowflake connection string connects GX to the Snowflake database.

The following code is an example of a Snowflake connection string format:

 my_connection_string = "snowflake://<USER_NAME>:<PASSWORD>@<ACCOUNT_NAME_OR_LOCATOR>/<DATABASE_NAME>/<SCHEMA_NAME>?warehouse=<WAREHOUSE_NAME>&role=<ROLE_NAME>"

Account Names and Locators

Snowflake accepts both account names and account locators as valid account identifiers when constructing a connection string.

Account names uniquely identify an account within your organization and are the preferred method of account identification.

Account locators act in the same manner but are auto-generated by Snowflake based on the cloud platform and region used.

For more information on both methods, please visit Snowflake's official documentation on account identifiers

Create a Snowflake Data Source

1. Run the following Python code to set the name and connection_string variables:

   datasource_name = "my_snowflake_datasource"

2. Run the following Python code to create a Snowflake Data Source:

   datasource = context.sources.add_snowflake(
       name=datasource_name, 
       connection_string=my_connection_string, # Or alternatively, individual connection args
   )

Passing individual connection arguments instead of connection_string

Although a connection string is the standard way to yield a connection to a database, the Snowflake datasource supports individual connection arguments to be passed in as an alternative.

The following arguments are supported:

• account
• user
• password
• database
• schema
• warehouse
• role
• numpy

Passing these values as keyword args to add_snowflake is functionally equivalent to passing in a connection_string.

For more information, check out Snowflake's official documentation on the Snowflake SQLAlchemy toolkit.

Connect to the data in a table (Optional)

1. Run the following Python code to set the asset_name and asset_table_name variables:

   asset_name = "my_asset"
   asset_table_name = my_table_name

2. Run the following Python code to create the Data Asset:

   table_asset = datasource.add_table_asset(name=asset_name, table_name=asset_table_name)

Connect to the data in a query (Optional)

1. Run the following Python code to define a Query Data Asset:

   asset_name = "my_query_asset"
   query = "SELECT * from yellow_tripdata_sample_2019_01"

2. Run the following Python code to create the Data Asset:

   query_asset = datasource.add_query_asset(name=asset_name, query=query)

Add additional tables or queries (Optional)

Repeat the previous steps to add additional Data Assets.

Databricks SQL

Databricks SQL

Connect GX to Databricks to access source data.

Prerequisites

• An installation of GX set up to work with SQL
• Source data stored in a Databricks cluster

Import GX and instantiate a Data Context

Run the following Python code to import GX and instantiate a Data Context:

import great_expectations as gx

context = gx.get_context()

Determine your connection string

The following code examples use a Databricks SQL connection string. A connection string connects GX to Databricks.

The following code is an example of a Databricks SQL connection string format:

my_connection_string = f"databricks://token:{token}@{host}:{port}/{database}?http_path={http_path}&catalog={catalog}&schema={schema}"

Create a Databricks SQL Data Source

1. Run the following Python code to set the name and connection_string variables:

   datasource_name = "my_databricks_sql_datasource"

2. Run the following Python code to create a Snowflake Data Source:

   datasource = context.sources.add_databricks_sql(
       name=datasource_name, 
       connection_string=my_connection_string,
   )

Connect to the data in a table (Optional)

1. Run the following Python code to set the asset_name and asset_table_name variables:

   asset_name = "my_asset"
   asset_table_name = my_table_name

2. Run the following Python code to create the Data Asset:

   table_asset = datasource.add_table_asset(name=asset_name, table_name=asset_table_name)

Connect to the data in a query (Optional)

1. Run the following Python code to define a Query Data Asset:

   asset_name = "my_query_asset"
   query = "SELECT * from yellow_tripdata_sample_2019_01"

2. Run the following Python code to create the Data Asset:

   query_asset = datasource.add_query_asset(name=asset_name, query=query)

Add additional tables or queries (Optional)

Repeat the previous steps to add additional Data Assets.

Related documentation

• How to organize Batches in a SQL based Data Asset

• How to request data from a Data Asset

• Use a Data Asset to create Expectations while interactively evaluating a set of data

• Use the Onboarding Data Assistant to evaluate one or more Batches of data and create Expectations