Snowflake Ingestion failing on Schemas with thousands of objects

[shcd-garjo3]

Describe the bug
Snowflake ingestion is failing for schemas with more than 10,000 views.

To Reproduce
Steps to reproduce the behavior:

1. Ingest Snowflake schema with ~16,000 views, with debugging enabled on the UI.

Expected behavior
You will see the following error:
[2024-01-18 21:12:02,501] DEBUG {datahub.ingestion.source.snowflake.snowflake_v2:838} - Failed to get views for schema FOOBAR.BAZ due to error 090153 (22000): The result set size exceeded the max number of rows(10000) supported for SHOW statements. Use LIMIT option to limit result set to a smaller number.
Traceback (most recent call last):

This is the complete error log:

Traceback (most recent call last):
File "/tmp/datahub/ingest/venv-snowflake-0.12.0/lib/python3.10/site-packages/datahub/ingestion/source/snowflake/snowflake_schema.py", line 307, in get_views_for_database
cur = self.query(SnowflakeQuery.show_views_for_database(db_name))
File "/tmp/datahub/ingest/venv-snowflake-0.12.0/lib/python3.10/site-packages/datahub/ingestion/source/snowflake/snowflake_utils.py", line 41, in query
resp = self.get_connection().cursor(DictCursor).execute(query)
File "/tmp/datahub/ingest/venv-snowflake-0.12.0/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 1132, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/tmp/datahub/ingest/venv-snowflake-0.12.0/lib/python3.10/site-packages/snowflake/connector/errors.py", line 290, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/tmp/datahub/ingest/venv-snowflake-0.12.0/lib/python3.10/site-packages/snowflake/connector/errors.py", line 345, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/tmp/datahub/ingest/venv-snowflake-0.12.0/lib/python3.10/site-packages/snowflake/connector/errors.py", line 221, in default_errorhandler
raise error_class(
snowflake.connector.errors.ProgrammingError: 090153 (22000): The result set size exceeded the max number of rows(10000) supported for SHOW statements. Use LIMIT option to limit result set to a smaller number.
[2024-01-18 21:12:00,128] DEBUG {datahub.ingestion.source.snowflake.snowflake_schema:40} - Query : show views in schema "FOOBAR"."BAZ";
[2024-01-18 21:12:02,501] DEBUG {datahub.ingestion.source.snowflake.snowflake_v2:838} - Failed to get views for schema FOOBAR.BAZ due to error 090153 (22000): The result set size exceeded the max number of rows(10000) supported for SHOW statements. Use LIMIT option to limit result set to a smaller number.
Traceback (most recent call last):
File "/tmp/datahub/ingest/venv-snowflake-0.12.0/lib/python3.10/site-packages/datahub/ingestion/source/snowflake/snowflake_v2.py", line 820, in fetch_views_for_schema
for view in self.get_views_for_schema(schema_name, db_name):

Screenshots
N/A

Desktop (please complete the following information):

• Running on EKS
• Browser [Edge]
• DataHub version 12.0.1

Additional context
Other schemas with fewer views are being ingested.

[hsheth2]

We looked into this a bit more, and it looks like the Snowflake SHOW VIEWS query supports pagination by combining the LIMIT and FROM clauses.

Implementing this would require modifying the get_views_for_database and the show_views_for_database methods.

Is this something that you'd be up for contributing?

[shcd-garjo3]

Hi Harshal, yes I would like to work on this. I will ping you on slack for a quick overview of what I need to do if that is okay.