WHERE clause isn't being applied in `_refine_coltypes` (Errors on Bigquery table with Partition filter set to required)

[tripathiabhay09]

Hi Team,

I am trying to run count_and_checksum and table_diff on a bq table having Partition filter flag set to Required.(see the screenshot for flag detail)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/operators/python.py", line 171, in execute
    return_value = self.execute_callable()
  File "/home/airflow/.local/lib/python3.8/site-packages/airflow/operators/python.py", line 189, in execute_callable
    return self.python_callable(*self.op_args, **self.op_kwargs)

  File "/home/airflow/.local/lib/python3.8/site-packages/data_diff/diff_tables.py", line 151, in with_schema
    return self._with_raw_schema(self.database.query_table_schema(self.table_path))
  File "/home/airflow/.local/lib/python3.8/site-packages/data_diff/diff_tables.py", line 143, in _with_raw_schema
    schema = self.database._process_table_schema(self.table_path, raw_schema, self._relevant_columns)
  File "/home/airflow/.local/lib/python3.8/site-packages/data_diff/databases/base.py", line 195, in _process_table_schema
    self._refine_coltypes(path, col_dict)
  File "/home/airflow/.local/lib/python3.8/site-packages/data_diff/databases/base.py", line 208, in _refine_coltypes
    samples_by_row = self.query(Select(fields, TableName(table_path), limit=16), list)
  File "/home/airflow/.local/lib/python3.8/site-packages/data_diff/databases/base.py", line 101, in query
    res = self._query(sql_code)
  File "/home/airflow/.local/lib/python3.8/site-packages/data_diff/databases/bigquery.py", line 57, in _query
    raise ConnectError(msg % (sql_code, e))
data_diff.databases.base.ConnectError: Exception when trying to execute SQL code:
    SELECT TRIM() FROM  LIMIT 16

Got error: 400 Cannot query over table<tablename> without a filter over column(s) <column name> that can be used for partition elimination

Not sure if this is a bug or I am doing something wrong. Currently, I am creating table segment by providing a where_clause which matches with the filter column on BQ.
Thank you so much for the help.

[erezsh]

Hi, I'm a little confused.. SELECT TRIM() FROM LIMIT 16 isn't a valid SQL statement, and data-diff shouldn't produce it. Is that the exact error you got, or did you edit it?

[tripathiabhay09]

sorry, I have edited it to remove sensitive information.

syntax is SELECT TRIM(key_column) FROM dataset.table_name LIMIT 16

[erezsh]

I see. Before the diff starts, data-diff samples some of the columns to find their type, but it doesn't use the where option when doing so. I think we can call it a bug.

[tripathiabhay09]

Thank you so much. I will wait for the fix.

[erezsh]

@tripathiabhay09 I believe I fixed this issue in PR #224 .

If you could test it and let me know, that would be helpful!

Install it with:

pip install git+https://github.com/datafold/data-diff.git@refs/pull/224/head

(you may have to pip uninstall data-diff first)

[tripathiabhay09]

sure, I will test it and let you know. Thanks!

[tripathiabhay09]

Hi @erezsh I have tested the change and I can confirm its working fine.
Thank you for help!

[erezsh]

Excellent, thank you!