How to configure a DataConnector for splitting and sampling tables in SQL
This guide will help you configure Splitting and Sampling for working with tables in an SQL database using
SimpleSqlalchemyDatasource, which operates as a proxy to InferredAssetSqlDataConnector and
ConfiguredAssetSqlDataConnector.
We will use the tables section of the SimpleSqlalchemyDatasource configuration, which exercises the
ConfiguredAssetSqlDataConnector, to showcase Splitting and Sampling (the same Splitting and Sampling
configuration options can be readily applied to the introspection section of the SimpleSqlalchemyDatasource
configuration, which exercises the InferredAssetSqlDataConnector).
The Splitting and Sampling mechanisms provided by Great Expectations serve as additional tools for Partitioning
your data at various levels of granularity:
Splittingprovides the means of focusing the BatchA selection of records from a Data Asset. data on the values of certain dimensions of the data of interest.Samplingprovides a means for reducing the amount of data in the retrieved batch to facilitate data analysis.
Prerequisites: This how-to guide assumes you have:
- Completed the Getting Started Tutorial
- Have a working installation of Great Expectations
- Configured and loaded a Data Context
- Configured a Datasource and Data Connector
- Reviewed How to configure a DataConnector to introspect and partition tables in SQL
This guide will use the tables section that is part of the following SimpleSqlalchemyDatasource configuration as an
example:
datasource_yaml = f"""
name: taxi_datasource
class_name: SimpleSqlalchemyDatasource
connection_string: <CONNECTION_STRING>
introspection: # Each key in the "introspection" section is an InferredAssetSqlDataConnector
whole_table:
include_schema_name: true
introspection_directives:
include_views: true
skip_inapplicable_tables: true # skip and continue upon encountering introspection errors
excluded_tables: # a list of tables to ignore when inferring data asset_names
- main.yellow_tripdata_sample_2019_03 # format: schema_name.table_name
daily:
introspection_directives:
include_views: true
skip_inapplicable_tables: true # skip and continue upon encountering introspection errors
include_schema_name: true
included_tables: # only include tables in this list when inferring data asset_names
- main.yellow_tripdata_sample_2019_01 # format: schema_name.table_name
splitter_method: _split_on_converted_datetime
splitter_kwargs:
column_name: pickup_datetime
date_format_string: "%Y-%m-%d"
hourly:
introspection_directives:
include_views: true
skip_inapplicable_tables: true
include_schema_name: true
included_tables: # only include tables in this list when inferring data asset_names
- main.yellow_tripdata_sample_2019_01 # format: schema_name.table_name
splitter_method: _split_on_converted_datetime
splitter_kwargs:
column_name: pickup_datetime
date_format_string: "%Y-%m-%d %H"
tables: # Each key in the "tables" section is a table_name (key name "tables" in "SimpleSqlalchemyDatasource" configuration is reserved).
# data_asset_name is: concatenate(data_asset_name_prefix, schema_name, table_name, data_asset_name_suffix)
yellow_tripdata_sample_2019_01: # Must match table name exactly.
partitioners: # Each key in the "partitioners" sub-section the name of a ConfiguredAssetSqlDataConnector (key name "partitioners" in "SimpleSqlalchemyDatasource" configuration is reserved).
whole_table:
include_schema_name: true
schema_name: main
data_asset_name_prefix: taxi__
data_asset_name_suffix: __asset
by_num_riders:
include_schema_name: true
schema_name: main
data_asset_name_prefix: taxi__
data_asset_name_suffix: __asset
splitter_method: _split_on_column_value
splitter_kwargs:
column_name: passenger_count
by_num_riders_random_sample:
include_schema_name: true
schema_name: main
data_asset_name_prefix: taxi__
data_asset_name_suffix: __asset
splitter_method: _split_on_column_value
splitter_kwargs:
column_name: passenger_count
sampling_method: _sample_using_random
sampling_kwargs:
p: 1.0e-1
"""
Preliminary Steps
1. Instantiate your project's DataContext
Import these necessary packages and modules.
import great_expectations as ge
Load your DataContext into memory using the get_context() method.
context = ge.get_context()
2. Configure your Datasource
Using the above example configuration, specify the connection string for your database. Then run this code to test your configuration:
context.test_yaml_config(datasource_yaml)
Feel free to adjust your configuration and re-run test_yaml_config() as needed.
3. Save the Datasource configuration to your DataContext
Save the configuration into your DataContext by using the add_datasource() function.
context.add_datasource(**yaml.load(datasource_yaml))
Splitting and Sampling
To configure Splitting, specify a dimension (i.e., column_name or column_names), the method of Splitting, and
parameters to be used by the specified Splitting method. In the present example, the Data ConnectorsProvides the configuration details based on the source data system which are needed by a Datasource to define Data Assets. named
by_num_riders and by_num_riders_random_sample split the table yellow_tripdata_sample_2019_01 on the column name
passenger_count using the _split_on_column_value method of Splitting.
To configure Sampling, specify the method of Sampling, and parameters to be used by the specified Sampling method.
In the present example, the Data Connector named by_num_riders_random_sample samples the table
yellow_tripdata_sample_2019_01 using the _sample_using_random method of Sampling, configured to return 10% of
the rows sampled at random, which is specified by the parameter p (stands for "proportion") set to the value 0.1.
Finally, confirm the expected number of batches was retrieved and the reduced size of a batch (due to Sampling):
datasource_name="taxi_datasource",
data_connector_name="by_num_riders_random_sample",
data_asset_name="<YOUR_DATA_ASSET_NAME>",
)
(set data_asset_name to "taxi__yellow_tripdata_sample_2019_01__asset" for the present example).
assert len(batch_list) == 6 # ride occupancy ranges from 1 passenger to 6 passengers
batch_data = batch_list[1].data # 2-passenger sample of batch data
num_rows = batch_data.execution_engine.engine.execute(
sa.select([sa.func.count()]).select_from(batch_data.selectable)
).scalar()
assert num_rows < 200
Additional Notes
Available Splitting methods and their configuration parameters:
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| **Method** | **Parameters** | **Returned Batch Data** |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| _split_on_whole_table | N/A | identical to original |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| _split_on_column_value | table_name='table', column_name='col' | rows where value of column_name are same |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| _split_on_converted_datetime | table_name='table', column_name='col', date_format_string=<'%Y-%m-%d'> | rows where value of column_name converted to datetime using the given date_format_string are same |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| _split_on_divided_integer | table_name='table', column_name='col', divisor=<int> | rows where value of column_name divided (using integral division) by the given divisor are same |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| _split_on_mod_integer | table_name='table', column_name='col', mod=<int> | rows where value of column_name divided (using modular division) by the given mod are same |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| _split_on_multi_column_values | table_name='table', column_names='<list[col]>' | rows where values of column_names are same |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------------+
| _split_on_hashed_column | column_name='col', | rows where value of column_name hashed (using "md5" hash function) are same (experimental) |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
Available Sampling methods and their configuration parameters:
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| **Method** | **Parameters** | **Returned Batch Data** |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| _sample_using_limit | n=num_rows | first up to to n (specific limit parameter) rows of batch |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| _sample_using_random | p=fraction | rows selected at random, whose number amounts to selected fraction of total number of rows in batch |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| _sample_using_mod | column_name='col', mod=<int> | take the mod of named column, and only keep rows that match the given value |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| _sample_using_a_list | column_name='col', value_list=<list[val]> | match the values in the named column against value_list, and only keep the matches |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
| _sample_using_hash | column_name='col', hash_digits=<int>, hash_value=<str> | hash the values in the named column (using "md5" hash function), and only keep rows that match the given hash_value |
+-----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------+
To view the full script used in this page, see it on GitHub: