Thread
#secoda-support
    b

    bulky-kitchen-43203

    6 months ago
    Hello, unable to get past Integration Snowflake "Lineage Extractor". Used to work, something change?
    Here is the full error:
    Error: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error: syntax error line 19 at position 35 unexpected ')'. [SQL: SELECT REGEXP_REPLACE(query_text ,'\\s+', ' ') AS formatted_query_text, database_name, schema_name, end_time, query_type FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE DATE(CONVERT_TIMEZONE('UTC', start_time)) = DATEADD(DAY, -1, CURRENT_DATE) AND schema_id IS NOT NULL AND is_client_generated_statement = FALSE AND (lower(query_text) NOT ILIKE '%%.query_history%%') AND execution_status = 'SUCCESS' AND query_type NOT IN ('SHOW', 'COMMIT', 'DESCRIBE', 'ROLLBACK', 'CREATE_STREAM', 'DROP_STREAM', 'BEGIN_TRANSACTION', 'GRANT', 'ALTER_SESSION', 'USE', 'ALTER_NETWORK_POLICY', 'ALTER_ACCOUNT', 'ALTER_TABLE_DROP_CLUSTERING_KEY', 'ALTER_USER', 'CREATE_CUSTOMER_ACCOUNT', 'CREATE_NETWORK_POLICY', 'CREATE_ROLE', 'CREATE_USER', 'DESCRIBE_QUERY', 'DROP_NETWORK_POLICY', 'DROP_ROLE', 'DROP_USER', 'LIST_FILES', 'REMOVE_FILES', 'REVOKE', 'DROP') AND lower(schema_name) IN () QUALIFY ROW_NUMBER() over (PARTITION BY formatted_query_text ORDER BY formatted_query_text) = 1 ORDER BY end_time; ] (Background on this error at: <https://sqlalche.me/e/14/f405>) Traceback (most recent call last): File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1803, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.7/site-packages/snowflake/connector/cursor.py", line 782, in execute self.connection, self, ProgrammingError, errvalue File "/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 273, in errorhandler_wrapper error_value, File "/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 324, in hand_to_other_handler cursor.errorhandler(connection, cursor, error_class, error_value) File "/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 210, in default_errorhandler cursor=cursor, snowflake.connector.errors.ProgrammingError: 001003 (42000): SQL compilation error: syntax error line 19 at position 35 unexpected ')'. The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/app/databuilder/job/job.py", line 91, in launch self._init() File "/app/databuilder/job/job.py", line 70, in _init self.task.init(self.conf) File "/app/databuilder/task/task.py", line 46, in init self.extractor.init(Scoped.get_scoped_conf(conf, self.extractor.get_scope())) File "/app/databuilder/extractor/database/snowflake/snowflake_lineage_extractor.py", line 93, in init conf, self.sql_stmt) File "/app/databuilder/extractor/sql_alchemy_extractor.py", line 98, in from_surrounding_config ae.init(c) File "/app/databuilder/extractor/sql_alchemy_extractor.py", line 35, in init self._execute_query() File "/app/databuilder/extractor/sql_alchemy_extractor.py", line 60, in _execute_query self.results = self.connection.execute(self.extract_sql) File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1279, in execute future=False, File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1585, in _exec_driver_sql distilled_parameters, File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context e, statement, parameters, cursor, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2027, in _handle_dbapi_exception sqlalchemy_exception, with_traceback=exc_info[2], from_=e File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1803, in _execute_context cursor, statement, parameters, context File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute cursor.execute(statement, parameters) File "/usr/local/lib/python3.7/site-packages/snowflake/connector/cursor.py", line 782, in execute self.connection, self, ProgrammingError, errvalue File "/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 273, in errorhandler_wrapper error_value, File "/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 324, in hand_to_other_handler cursor.errorhandler(connection, cursor, error_class, error_value) File "/usr/local/lib/python3.7/site-packages/snowflake/connector/errors.py", line 210, in default_errorhandler cursor=cursor, sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error: syntax error line 19 at position 35 unexpected ')'. [SQL: SELECT REGEXP_REPLACE(query_text ,'\\s+', ' ') AS formatted_query_text, database_name, schema_name, end_time, query_type FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE DATE(CONVERT_TIMEZONE('UTC', start_time)) = DATEADD(DAY, -1, CURRENT_DATE) AND schema_id IS NOT NULL AND is_client_generated_statement = FALSE AND (lower(query_text) NOT ILIKE '%%.query_history%%') AND execution_status = 'SUCCESS' AND query_type NOT IN ('SHOW', 'COMMIT', 'DESCRIBE', 'ROLLBACK', 'CREATE_STREAM', 'DROP_STREAM', 'BEGIN_TRANSACTION', 'GRANT', 'ALTER_SESSION', 'USE', 'ALTER_NETWORK_POLICY', 'ALTER_ACCOUNT', 'ALTER_TABLE_DROP_CLUSTERING_KEY', 'ALTER_USER', 'CREATE_CUSTOMER_ACCOUNT', 'CREATE_NETWORK_POLICY', 'CREATE_ROLE', 'CREATE_USER', 'DESCRIBE_QUERY', 'DROP_NETWORK_POLICY', 'DROP_ROLE', 'DROP_USER', 'LIST_FILES', 'REMOVE_FILES', 'REVOKE', 'DROP') AND lower(schema_name) IN () QUALIFY ROW_NUMBER() over (PARTITION BY formatted_query_text ORDER BY formatted_query_text) = 1 ORDER BY end_time; ] (Background on this error at: <https://sqlalche.me/e/14/f405>)
    Was able to extract the offending query and found Syntax Error?
    e

    elegant-house-93198

    6 months ago
    @bulky-kitchen-43203 could you go to the “Schemas” tab in Secoda and see if there are any schemas. If there aren’t any schemas could you click “Refresh”
    b

    bulky-kitchen-43203

    6 months ago
    Ok ... Wow. Now there are. Thanks!