Great_expectations: Batch request data asset name for postgres datasource cannot include schema

[ryanaustincarlson]

(Updating here to note that this is a Not a Contribution)

Describe the bug
In v0.13.7, I'm trying to specify a table in a custom schema to validate against. I've tried to set the data asset name as schema_name.table_name but that gives me an error here:

https://github.com/great-expectations/great_expectations/blob/903237f009cd98f93f4607b676568e72b72ade4c/great_expectations/datasource/data_connector/configured_asset_sql_data_connector.py#L139-L152

It seems that the _data_references_cache dict has keys that are the table name without the schema.

I'm not sure if this is a real bug or if I'm doing something wrong and there's an easy way to specify the schema as part of the batch request (or even in the datasources definition, which would also be fine for me).

To Reproduce
Steps to reproduce the behavior:

1. Spin up a postgres instance (e.g., with docker as below)

docker run \
    -e POSTGRES_USER=great \
    -e POSTGRES_PASSWORD=expectations \
    -e POSTGRES_DB=ge \
    -p "5432:5432" \
    postgres:13.1

1. Connect to the test db...

PGPASSWORD=expectations psql -h localhost -p 5432 -U great -d ge

...and create some fake data

CREATE SCHEMA testing;

CREATE TABLE testing.prices (
    id VARCHAR(64),
    category VARCHAR(54),
    name VARCHAR(64),
    price NUMERIC
);

-- everything the body needs
INSERT INTO testing.prices (id, category, name, price)
VALUES
    ('abc', 'cheese',   'cheddar',          10),
    ('jkl', 'cheese',   'gouda',            15),
    ('mno', 'cereal',   'cheerios',         3),
    ('pqr', 'cereal',   'froot loops',      2),
    ('stu', 'cereal',   'frosted flakes',   4);

1. Start a fresh project with this config file

config_version: 2.0

datasources:
  my_postgres_db:
    credentials:
      drivername: postgresql
      host: localhost
      port: '5432'
      username: great
      password: expectations
      database: ge
    module_name: great_expectations.datasource
    class_name: SimpleSqlalchemyDatasource
    introspection:
      whole_table: { }

config_variables_file_path: uncommitted/config_variables.yml

plugins_directory: plugins/

validation_operators:
  action_list_operator:
    class_name: ActionListValidationOperator
    action_list:
      - name: store_validation_result
        action:
          class_name: StoreValidationResultAction
      - name: store_evaluation_params
        action:
          class_name: StoreEvaluationParametersAction
      - name: update_data_docs
        action:
          class_name: UpdateDataDocsAction

stores:
  expectations_store:
    class_name: ExpectationsStore
    store_backend:
      class_name: TupleFilesystemStoreBackend
      base_directory: expectations/

  validations_store:
    class_name: ValidationsStore
    store_backend:
      class_name: TupleFilesystemStoreBackend
      base_directory: uncommitted/validations/

  evaluation_parameter_store:
    class_name: EvaluationParameterStore

expectations_store_name: expectations_store
validations_store_name: validations_store
evaluation_parameter_store_name: evaluation_parameter_store

data_docs_sites:
  local_site:
    class_name: SiteBuilder
    show_how_to_buttons: true
    store_backend:
      class_name: TupleFilesystemStoreBackend
      base_directory: uncommitted/data_docs/local_site/
    site_index_builder:
      class_name: DefaultSiteIndexBuilder

1. Run this script to get the error

from pprint import pprint

from great_expectations import get_context
from great_expectations.core import ExpectationConfiguration
from great_expectations.core.batch import BatchRequest

context = get_context()
suite = context.create_expectation_suite('testing123', overwrite_existing=True)

suite.add_expectation(ExpectationConfiguration(
    expectation_type='expect_column_to_exist',
    kwargs={
        'column': 'id',
    },
))

batch_request = BatchRequest(
    datasource_name='my_postgres_db',
    data_asset_name='testing.prices',  # this fails
    # data_asset_name='prices',  # this works
    data_connector_name='whole_table',
)

validator = context.get_validator(
    batch_request=batch_request,
    expectation_suite=suite,
)

results = context.run_validation_operator(
    validation_operator_name='action_list_operator',
    assets_to_validate=[validator],
)

print(f'Success? {results.success}')
pprint(results)

Expected behavior

I expect the validation sequence to run through successfully and generate results. Note that if you replace data_asset_name in the BatchRequest constructor with 'prices' instead of 'testing.prices', it works as expected.

Environment (please complete the following information):

• Operating System: MacOS 10.15.4
• Great Expectations Version: 0.13.7 (also tested on 0.13.4 with the same results)

Additional context
Again, I'm wondering if there's some other way to specify what schema I want -- a couple thoughts come to mind

• is there another param that BatchRequest takes?
• can I specify this in the introspection or tables section of the datasources definition? I"m not sure I quite understand what the purposes of those keys are 😅
• is there a way to run a custom sql query (similar to the query arg from batch_kwargs in v0.12)?

[eugmandel]

@ryanaustincarlson Thank you for following up and filing this! Adding to the queue to be addressed by the core team.

[Shinnnyshinshin]

Hi @ryanaustincarlson first i wanted to thank you for this submission. It's honestly one of the most elegantly written and well documented issues I've had a chance to work with.

A fix with associated tests are part of PR #2465 , which is currently being reviewed by the core team. You'll also want to include the following include_schema_name flag in your config.

datasources:
  my_postgres_db:
    credentials:
      drivername: postgresql
      host: localhost
      port: '5432'
      username: great
      password: expectations
      database: ge
    module_name: great_expectations.datasource
    class_name: SimpleSqlalchemyDatasource
    introspection:
      whole_table:
        include_schema_name: True

That way data_asset_name will include the schema name and you'll be able to refer to testing.prices. Thank you again for your submission, and I hope we get to work together again soon.

[mortenbpost]

@Shinnnyshinshin Has this fix been included in a release yet? Also, is there a way to use a custom query like in v0.12 ?