Manage SQL Data Assets
A Data Asset is a collection of records within a Data Source that define how Great Expectations (GX) organizes data into Batches. Use the information provided here to connect GX to SQL tables and data returned by SQL database queries and learn how to organize Batches in a SQL Data Asset.
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.
If you're using a Data Source created with the block-config method, see How to configure a SQL Data Source with the block-config method.
- Connect to a SQL table
- Connect to SQL data using a query
- Organize Batches
Connect to a SQL table
Connect GX to a SQL table to access source data.
The following code examples use a previously
defined Data Source named
"my_datasource" to
connect to a SQL database.
Prerequisites
- An installation of GX set up to work with SQL
- Source data stored in a SQL database
- A SQL-based Data Source
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()
Retrieve a SQL Data Source
Run the following Python code to retrieve the Data Source:
datasource = context.get_datasource("my_datasource")
Add a table to the Data Source as a Data Asset
You create a Data Asset to identify the table to connect to.
Run the following Python code to define the
name and
table_name variables:
table_asset = datasource.add_table_asset(name="my_asset", table_name=my_table_name)
Add additional tables (Optional)
To connect to additional tables in the same SQL Database, repeat the previous steps to add them as table Data Assets.
Related documentation
Connect to SQL data using a query
Connect GX to the data returned by a query in a SQL database.
The following code examples use a previously
defined Data Source named
"my_datasource" to
connect to a SQL database.
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()
Retrieve a SQL Data Source
Run the following Python code to retrieve the Data Source:
datasource = context.get_datasource("my_datasource")
Add a query to the Data Source as a Data Asset
Run the following Python code to define a Data
Asset and the name and
query variables:
table_asset = datasource.add_query_asset(
name="my_asset",
query="SELECT passenger_count, total_amount FROM yellow_tripdata_sample_2019_01",
)
Add additional queries (Optional)
To connect to the contents of additional queries in the same SQL Database, repeat the previous steps to add them as query Data Assets.
Related documentation
Organize Batches
Organize Batches in a SQL-based Data Asset. This includes using Splitters to divide the data in a table or query based on the contents of a provided field and adding Batch Sorters to a Data Asset to specify the order in which Batches are returned.
The following code examples use a previously
defined Data Source named
"my_datasource" to
connect to a SQL database.
Prerequisites
- A working installation of Great Expectations
- A Data Asset in a SQL-based Data Source
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()
Retrieve a SQL Data Source and Data Asset
Run the following Python code to retrieve the Data Source:
my_datasource = context.get_datasource("my_datasource")
my_table_asset = my_datasource.get_asset(asset_name="my_table_asset")
Add a Splitter to the Data Asset
Run the following Python code to split the TableAsset into Batches:
my_table_asset.add_splitter_year_and_month(column_name="pickup_datetime")
Add Batch Sorters to the Data Asset (Optional)
Adding Batch Sorters to your Data Asset lets you
explicitly state the order in which your Batches
are returned when you request data from the Data
Asset. To add Batch Sorters, pass a list of
sorters to the
add_sorters(...) method of your
Data Asset.
Run the following Python code to split the
"pickup_datetime" column
on "year" and
"month", so your list of
sorters can have up to two elements. The code
also adds an ascending sorter based on the
contents of the splitter group
"year" and a descending
sorter based on the contents of the splitter
group "month":
my_asset = my_table_asset.add_sorters(["+year", "-month"])
Use a Batch Request to verify Data Asset functionality
Run the following Python code to verify that your Data Asset returns the necessary files as Batches:
my_batch_request = my_table_asset.build_batch_request()
batches = my_table_asset.get_batch_list_from_batch_request(my_batch_request)
A Batch List can contain a lot of metadata. To
verify which files were included in the returned
Batches, run the following Python code to review
the batch_spec for each returned
Batch:
for batch in batches:
print(batch.batch_spec)