https://datahubproject.io logo
Join Slack
Powered by
hello, I was posting a question regarding connecti...
# ingestion
c
hello, I was posting a question regarding connection to snowflake and @incalculable-ocean-74010 helped me with specifying the correct endpoint for my recipe. https://datahubspace.slack.com/archives/CUMUWQU66/p1648554749148879 now I managed to connect to snowflake but I have an issue with getting the metadata. The ingestion job takes about 1.5h and then fails. The stacktrace is the following ( I trimmed it a bit). Could you please give me a hint what can be checked here?
Copy code
'[2022-03-30 16:24:11,842] ERROR    {datahub.entrypoints:152} - File '
           '"/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/datahub/entrypoints.py", line 138, in main\n'
           '    135  def main(**kwargs):\n'
           '    136      # This wrapper prevents click from suppressing errors.\n'
           '    137      try:\n'
           '--> 138          sys.exit(datahub(standalone_mode=False, **kwargs))\n'
           '    139      except click.exceptions.Abort:\n'
           '    ..................................................\n'
           '     kwargs = {}\n'
           '     datahub = <Group datahub>\n'
           "     click.exceptions.Abort = <class 'click.exceptions.Abort'>\n"
           '    ..................................................\n'
           '\n'
           'File "/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/click/core.py", line 1130, in __call__\n'
           '    1128  def __call__(self, *args: t.Any, **kwargs: t.Any) -> t.Any:\n'
           ' (...)\n'
           '--> 1130      return self.main(*args, **kwargs)\n'
           '    ..................................................\n'
           '     self = <Group datahub>\n'
           '     args = ()\n'
           '     t.Any = typing.Any\n'
           "     kwargs = {'standalone_mode': False,\n"
           "               'prog_name': 'python3 -m datahub'}\n"
           '    ..................................................\n'
           '\n'
           'File "/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/click/core.py", line 1055, in main\n'
           '    rv = self.invoke(ctx)\n'
           'File "/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/click/core.py", line 1657, in invoke\n'
           '    return _process_result(sub_ctx.command.invoke(sub_ctx))\n'
........................
'File "/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/snowflake/sqlalchemy/snowdialect.py", '
           'line 573, in <listcomp>\n'
           '    return [self.normalize_name(row[1]) for row in cursor]\n'
           'File "/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/snowflake/sqlalchemy/snowdialect.py", '
           'line 204, in normalize_name\n'
           '    if name.upper() == name and not self.identifier_preparer._requires_quotes(name.lower()):\n'
           'File "/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 3613, '
           'in _requires_quotes\n'
           '    or value[0] in self.illegal_initial_characters\n'
           '\n'
           'IndexError: string index out of range\n'
           '[2022-03-30 16:24:11,842] INFO     {datahub.entrypoints:161} - DataHub CLI version: 0.8.31 at '
           '/tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/lib/python3.9/site-packages/datahub/__init__.py\n'
           '[2022-03-30 16:24:11,842] INFO     {datahub.entrypoints:164} - Python version: 3.9.9 (main, Dec 21 2021, 10:03:34) \n'
           '[GCC 10.2.1 20210110] at /tmp/datahub/ingest/venv-a8e48815-7f1f-4468-958c-3c2b1fcbf48e/bin/python3 on '
           'Linux-5.4.176-91.338.amzn2.x86_64-x86_64-with-glibc2.31\n'
           "[2022-03-30 16:24:11,842] INFO     {datahub.entrypoints:167} - GMS config {'models': {}, 'versions': {'linkedin/datahub': {'version': "
           "'v0.8.31', 'commit': '2f078c981c86b72145eebf621230ffd445948ef6'}}, 'managedIngestion': {'defaultCliVersion': '0.8.31', 'enabled': True}, "
           "'statefulIngestionCapable': True, 'supportsImpactAnalysis': True, 'telemetry': {'enabledCli': True, 'enabledIngestion': False}, "
           "'retention': 'true', 'noCode': 'true'}\n",
           "2022-03-30 16:24:14.167925 [exec_id=a8e48815-7f1f-4468-958c-3c2b1fcbf48e] INFO: Failed to execute 'datahub ingest'",
           '2022-03-30 16:24:14.168306 [exec_id=a8e48815-7f1f-4468-958c-3c2b1fcbf48e] INFO: Caught exception EXECUTING '
           'task_id=a8e48815-7f1f-4468-958c-3c2b1fcbf48e, name=RUN_INGEST, stacktrace=Traceback (most recent call last):\n'
           '  File "/usr/local/lib/python3.9/site-packages/acryl/executor/execution/default_executor.py", line 119, in execute_task\n'
           '    self.event_loop.run_until_complete(task_future)\n'
           '  File "/usr/local/lib/python3.9/site-packages/nest_asyncio.py", line 81, in run_until_complete\n'
           '    return f.result()\n'
           '  File "/usr/local/lib/python3.9/asyncio/futures.py", line 201, in result\n'
           '    raise self._exception\n'
           '  File "/usr/local/lib/python3.9/asyncio/tasks.py", line 256, in __step\n'
           '    result = coro.send(None)\n'
           '  File "/usr/local/lib/python3.9/site-packages/acryl/executor/execution/sub_process_ingestion_task.py", line 115, in execute\n'
           '    raise TaskError("Failed to execute \'datahub ingest\'")\n'
           "acryl.executor.execution.task.TaskError: Failed to execute 'datahub ingest'\n"]}
my recipe looks like this
Copy code
source:
    type: snowflake
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        warehouse: NON_PROD_VWH
        username: '${snowflake-user}'
        password: '${snowflake-pass}'
        options:
            case_sensitive:
                - true
        include_views: true
        schema_pattern:
            allow:
                - ^DATABASE1\$
                - ^DATABASE2\$
                - ^DATABASE3\$
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
I also tried this
Copy code
source:
    type: snowflake
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        username: '${snowflake-user}'
        password: '${snowflake-pass}'
        options:
            case_sensitive:
                - true
        include_views: true
        database_pattern:
            allow:
                - ^Database1$
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
s
Can you please try to update to the latest CLI version? We have had many fixes for snowflake merged in in 
0.8.31.x
fixes as recent as 2 days ago
c
thanks, will do now
s
If that does not solve the problem request you to please post the full stacktrace as a file
Copy code
datahub ingest ... > datahub.log 2>&1
and share with us the 
datahub.log
file (after masking any secrets)
c
should I run it from datahub-actions container? I’m running the job from datahub UI and I don’t run 
datahub ingest
directly
I don’t see minor version releases for 0.8.31.x The latest that I see is https://github.com/datahub-project/datahub/releases/tag/v0.8.31 could you please send me a link to that version that you’ve mentioned above?
s
c
thanks!
I’m installing datahub as a helm chart. I’ve just upgraded it from 0.2.62 to 0.2.65 https://github.com/acryldata/datahub-helm/tree/datahub-0.2.65 but I think that I didn’t pull the changes from 0.8.31.6 https://github.com/acryldata/datahub/tree/v0.8.31.6 how can I pull the latest changes to apply it in helm?
s
In the UI based ingestion? You can set the the CLI used by the ingestion through your helm 
values.yaml
like this https://github.com/acryldata/datahub-helm/blob/master/charts/datahub/values.yaml#L160 or you can use the Advanced Tab in the UI ingestion to override the CLI version. If you do this make sure to remove it in the next release otherwise it will always override to 
v0.8.31.6
c
In the UI based ingestion?
yes changed it in values file
Copy code
- name: UI_INGESTION_DEFAULT_CLI_VERSION
-               value: "0.8.31"
+               value: "0.8.31.6"
you can use the Advanced Tab in the UI ingestion to override the CLI version.
I can’t find this tab… Maybe I need to use other user. Now I logged in as a default 
datahub
user
s
The 3rd screen where you set the name of ingestion source there is Advanced Toggle. At the end you can set the CLI version
c
thank you. I set it now. Cancelling the running job (btw. it takes some time, it may take about 20 minutes or so for the job to be cancelled). When it’s cancelled I’ll re-run it with explicitly set cli version on that advanced tab
tried that. It didn’t help and I see the same issue as I posted. Later I went inside the datahub-actions container and ran it manually.
Copy code
datahub@datahub-acryl-datahub-actions-58f55687ff-rq65v:/$ datahub --debug ingest -c /tmp/recipe.yml >  /tmp/datahub.log 2>&1 &
I see that inside the datahub-actions which runs the jobs from datahub UI there’s an old CLI version (
0.8.28.1
). Anyway the error here is different from the one that I see when running it via UI. The recipe is the same though
Copy code
datahub@datahub-acryl-datahub-actions-58f55687ff-rq65v:/$
[1]+  Exit 1                  datahub --debug ingest -c /tmp/recipe.yml > /tmp/datahub.log 2>&1
datahub@datahub-acryl-datahub-actions-58f55687ff-rq65v:/$
datahub@datahub-acryl-datahub-actions-58f55687ff-rq65v:/$
datahub@datahub-acryl-datahub-actions-58f55687ff-rq65v:/$ cat /tmp/datahub.log
[2022-04-01 13:33:49,695] INFO     {datahub.cli.ingest_cli:70} - DataHub CLI version: 0.8.28.1
[2022-04-01 13:33:49,699] DEBUG    {datahub.cli.ingest_cli:76} - Using config: {'source': {'type': 'snowflake', 'config': {'host_port': '<http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>', 'username': 'sf-user', 'password': 'pass', 'options': {'case_sensitive': [False]}, 'include_views': False, 'database_pattern': {'allow': ['^DATABASE_NAME$']}}}, 'sink': {'type': 'datahub-rest', 'config': {'server': '<http://datahub-gms.datahub.svc.cluster.local:8080>'}}}
[2022-04-01 13:33:53,018] INFO     {datahub.ingestion.source.sql.snowflake:120} - using authenticator type 'DEFAULT_AUTHENTICATOR'
[2022-04-01 13:33:53,020] DEBUG    {datahub.ingestion.run.pipeline:125} - Source type:snowflake,<class 'datahub.ingestion.source.sql.snowflake.SnowflakeSource'> configured
[2022-04-01 13:33:53,030] DEBUG    {datahub.ingestion.run.pipeline:131} - Sink type:datahub-rest,<class 'datahub.ingestion.sink.datahub_rest.DatahubRestSink'> configured
[2022-04-01 13:33:53,030] INFO     {datahub.cli.ingest_cli:86} - Starting metadata ingestion
[2022-04-01 13:33:53,031] DEBUG    {datahub.ingestion.source.sql.snowflake:214} - sql_alchemy_url=<snowflake://sf-user>:pass@company_name.us-east-1.snowflakecomputing.com/?authenticator=SNOWFLAKE&application=acryl_datahub
[2022-04-01 13:33:53,032] INFO     {datahub.ingestion.source.sql.snowflake:223} - Checking current version
[2022-04-01 13:33:53,226] INFO     {datahub.cli.ingest_cli:88} - Finished metadata ingestion

Source (snowflake) report:
{'workunits_produced': 0,
 'workunit_ids': [],
 'warnings': {},
 'failures': {'version': ['Error: (snowflake.connector.errors.ProgrammingError) 251001: Account must be specified\n'
                          '(Background on this error at: <http://sqlalche.me/e/13/f405)']>},
 'cli_version': '0.8.28.1',
 'tables_scanned': 0,
 'views_scanned': 0,
 'entities_profiled': 0,
 'filtered': [],
 'soft_deleted_stale_entities': [],
 'query_combiner': None,
 'num_table_to_table_edges_scanned': 0,
 'num_table_to_view_edges_scanned': 0,
 'num_view_to_table_edges_scanned': 0,
 'num_external_table_edges_scanned': 0,
 'upstream_lineage': {},
 'saas_version': '',
 'role': '',
 'role_grants': []}
Sink (datahub-rest) report:
{'records_written': 0,
 'warnings': [],
 'failures': [],
 'downstream_start_time': None,
 'downstream_end_time': None,
 'downstream_total_latency_in_seconds': None,
 'gms_version': 'v0.8.31'}

Pipeline finished with failures
s
If there is another CLI installed by default then that does not mean managed ingestion is using that
Managed Ingestion will be using the latest version if that is specified in the helm charts
👍 1
I cannot help without the full logs. It does not have the required information to see where exactly it failed
c
these are all logs that I have from manual CLI command execution. If there’s any way to get more logs please let me know. I’ll send now those logs that I have from the UI ingestion job
s
Yes I was referencing the UI ingestion job with the latest CLI
👍 1
c
please find the log attached to this message
datahub_ingest_log-1.log
^^ this log is for the following recipe
Copy code
source:
    type: snowflake
    config:
        host_port: <http://company_name.eu-west-1.snowflakecomputing.com|company_name.eu-west-1.snowflakecomputing.com>
        username: username
        password: pass
        options:
            case_sensitive:
                - false
        include_views: false
        database_pattern:
            allow:
                - ^Database_Name$
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
s
Do you have special characters in your schema names? Would you be willing to share the schema name? I don't think we have tested with special characters in schema names. Or maybe there are non-ASCII characters in there
c
there were some schemas with 
_
and one schema with an empty name. We removed them and the job finished successfully. No metadata was ingested though. The job took about 1h and I see that it traversed a few databases (not those that I set in the 
database_pattern
) and finished
Copy code
~~~~ Execution Summary ~~~~

RUN_INGEST - {'errors': [],
 'exec_id': '523ddd24-503a-4272-8cfe-cbe78034c07c',
 'infos': ['2022-04-04 08:26:26.306204 [exec_id=523ddd24-503a-4272-8cfe-cbe78034c07c] INFO: Starting execution for task with name=RUN_INGEST',
           "2022-04-04 09:40:57.901143 [exec_id=523ddd24-503a-4272-8cfe-cbe78034c07c] INFO: stdout=                 'SELECT granted on TABLE "

...

           'Sink (datahub-rest) report:\n'
           "{'records_written': 0,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': None,\n"
           " 'downstream_end_time': None,\n"
           " 'downstream_total_latency_in_seconds': None,\n"
           " 'gms_version': 'v0.8.31'}\n"
           '\n'
           'Pipeline finished successfully\n',
           "2022-04-04 09:40:57.908984 [exec_id=523ddd24-503a-4272-8cfe-cbe78034c07c] INFO: Successfully executed 'datahub ingest'"]}
Execution finished successfully!
the recipe was
Copy code
source:
    type: snowflake
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        username: user
        password: pass
        options:
            case_sensitive:
                - false
        include_views: false
        database_pattern:
            allow:
                - ^Database_Name$
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
s
Without the full logs it will be hard me to help you out here.
How did you create a schema with empty name?
c
How did you create a schema with empty name?
I don’t know tbh. They’ve been removed already.
Without the full logs it will be hard me to help you out here.
I’ll try to prepare them. Need to mask some data
Hi, I’ve done the upgrade to datahub 0.8.32 and removed some schemas. Now I see that the ingestion job finished successfully and metadata was ingested. I see that lineage tab is not active for ingested tables though. Am I missing some option/flag?
Copy code
source:
    type: snowflake
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        username: '${SNOWFLAKE_USER}'
        password: '${SNOWFLAKE_PASS}'
        warehouse: NONPROD_VWH
        options:
            case_sensitive: true
        include_views: true
        include_tables: true
        env: NON_PROD
        report_upstream_lineage: true
        database_pattern:
            allow:
                - ^Database_Name$
            ignoreCase: false
        schema_pattern:
            allow:
                - ^Schema_name_1$
                - ^Schema_name_2$
            deny:
                - ^Schema_name_3$
            ignoreCase: false
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
btw. ingestion job still takes 4000-5000 seconds
s
Did you run this?
Copy code
grant imported privileges on database snowflake to role YOUR_ROLE;
c
thanks for your reply, I think I did, but let me double check
s
In the source report there would be list of roles under 
role_grants
. You can see it to verify whether you have it or not
c
also I created a recipe for 
snowflake-usage
Copy code
source:
    type: snowflake-usage
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        username: '${SNOWFLAKE_USER}'
        password: '${SNOWFLAKE_PASS}'
        env: NON_PROD
        format_sql_queries: true
        database_pattern:
            allow:
                - ^Database_Name$
            ignoreCase: false
        schema_pattern:
            ignoreCase: false
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
and it finishes successfully issues the following messages. That’s probably because we have three service users with emails set to 
null
. I don’t know how to exclude them from the recipe
Copy code
~~~ Execution Summary ~~~~

RUN_INGEST - {'errors': [],
 'exec_id': '1d74021a-b9db-4d85-9e74-38a099c4be7a',
 'infos': ['2022-04-07 09:29:50.345608 [exec_id=1d74021a-b9db-4d85-9e74-38a099c4be7a] INFO: Starting execution for task with name=RUN_INGEST',
           "2022-04-07 09:33:04.349021 [exec_id=1d74021a-b9db-4d85-9e74-38a099c4be7a] INFO: stdout=                        'email\\n'\n"
           "                        '  none is not an allowed value (type=type_error.none.not_allowed)',\n"
           '                        "Failed to parse usage line {\'query_start_time\': datetime.datetime(2022, 4, 6, 1, 58, 2, 317000, "\n'
           '                        "tzinfo=datetime.timezone.utc), \'query_text\': \'select name from "\n'
           '                        \'"DATABASE_NAME"."SnowflakeAdministration".users_without_sso\\\', \\\'query_type\\\': \\\'SELECT\\\', '
           "\\'rows_inserted\\': 0, '\n"
           '                        "\'rows_updated\': 0, \'rows_deleted\': 0, \'base_objects_accessed\': [], \'direct_objects_accessed\': [], '
           '\'user_name\': \'SYSTEM_USER\', "\n'
           '                        "\'first_name\': None, \'last_name\': None, \'display_name\': None, \'email\': None, \'role_name\': '
           '\'ACCOUNTADMIN\'}, 1 validation "\n'
           "                        'error for SnowflakeJoinedAccessEvent\\n'\n"
           "                        'email\\n'\n"
           "                        '  none is not an allowed value (type=type_error.none.not_allowed)',\n"
           '                        "Failed to parse usage line {\'query_start_time\': datetime.datetime(2022, 4, 6, 1, 58, 1, 821000, "\n'
           '                        "tzinfo=datetime.timezone.utc), \'query_text\': \'insert overwrite into "\n'
           '                        \'"DATABASE_NAME"."SnowflakeAdministration".users_without_sso\\\\n     select * from \'\n'
           '                        "table(result_scan(last_query_id()))\\\\n     where $16 = \\\\\'NON_PROD_ANALYST_ROLE\\\\\'\', '
           '\'query_type\': \'INSERT\', "\n'
           '                        "\'rows_inserted\': 0, \'rows_updated\': 0, \'rows_deleted\': 0, \'base_objects_accessed\': [], '
           '\'direct_objects_accessed\': [], "\n'
           '                        "\'user_name\': \'SYSTEM_USER\', \'first_name\': None, \'last_name\': None, \'display_name\': None, \'email\': None, '
           '\'role_name\': "\n'
           '                        "\'ACCOUNTADMIN\'}, 1 validation error for SnowflakeJoinedAccessEvent\\n"\n'
           "                        'email\\n'\n"
           "                        '  none is not an allowed value (type=type_error.none.not_allowed)',\n"
           '                        "Failed to parse usage line {\'query_start_time\': datetime.datetime(2022, 4, 6, 1, 56, 2, 556000, "\n'
           '                        "tzinfo=datetime.timezone.utc), \'query_text\': \'select name from "\n'
           '                        \'"DATABASE_NAME"."SnowflakeAdministration".users_without_sso\\\', \\\'query_type\\\': \\\'SELECT\\\', '
           "\\'rows_inserted\\': 0, '\n"
           '                        "\'rows_updated\': 0, \'rows_deleted\': 0, \'base_objects_accessed\': [], \'direct_objects_accessed\': [], '
           '\'user_name\': \'SYSTEM_USER\', "\n'
           '                        "\'first_name\': None, \'last_name\': None, \'display_name\': None, \'email\': None, \'role_name\': '
           '\'ACCOUNTADMIN\'}, 1 validation "\n'
           "                        'error for SnowflakeJoinedAccessEvent\\n'\n"
           "                        'email\\n'\n"
           "                        '  none is not an allowed value (type=type_error.none.not_allowed)',\n"
           '                        "Failed to parse usage line {\'query_start_time\': datetime.datetime(2022, 4, 6, 1, 56, 2, 18000, "\n'
           '                        "tzinfo=datetime.timezone.utc), \'query_text\': \'insert overwrite into "\n'
           '                        \'"DATABASE_NAME"."SnowflakeAdministration".users_without_sso\\\\n     select * from \'\n'
           '                        "table(result_scan(last_query_id()))\\\\n     where $16 = \\\\\'NON_PROD_ANALYST_ROLE\\\\\'\', '
           '\'query_type\': \'INSERT\', "\n'
           '                        "\'rows_inserted\': 0, \'rows_updated\': 0, \'rows_deleted\': 0, \'base_objects_accessed\': [], '
           '\'direct_objects_accessed\': [], "\n'
           '                        "\'user_name\': \'SYSTEM_USER\', \'first_name\': None, \'last_name\': None, \'display_name\': None, \'email\': None, '
           '\'role_name\': "\n'

           .............................

            " 'failures': {},\n"
           " 'cli_version': '0.8.32',\n"
           " 'cli_entry_location': '/tmp/datahub/ingest/venv-1d74021a-b9db-4d85-9e74-38a099c4be7a/lib/python3.9/site-packages/datahub/__init__.py',\n"
           " 'py_version': '3.9.9 (main, Dec 21 2021, 10:03:34) \\n[GCC 10.2.1 20210110]',\n"
           " 'py_exec_path': '/tmp/datahub/ingest/venv-1d74021a-b9db-4d85-9e74-38a099c4be7a/bin/python3',\n"
           " 'os_details': 'Linux-5.4.176-91.338.amzn2.x86_64-x86_64-with-glibc2.31',\n"
           " 'end_time': datetime.datetime(2022, 4, 8, 0, 0, tzinfo=datetime.timezone.utc),\n"
           " 'start_time': datetime.datetime(2022, 4, 6, 0, 0, tzinfo=datetime.timezone.utc),\n"
           " 'min_access_history_time': datetime.datetime(2021, 4, 1, 0, 0, 36, 883000, tzinfo=datetime.timezone.utc),\n"
           " 'max_access_history_time': datetime.datetime(2022, 4, 7, 9, 30, 5, 157000, tzinfo=datetime.timezone.utc),\n"
           " 'access_history_range_query_secs': 45.8,\n"
           " 'access_history_query_secs': 77.42,\n"
           " 'rows_processed': 163684,\n"
           " 'rows_missing_query_text': 145,\n"
           " 'rows_zero_base_objects_accessed': 163539,\n"
           " 'rows_zero_direct_objects_accessed': 163539,\n"
           " 'rows_missing_email': 0,\n"
           " 'rows_parsing_error': 3844}\n"
           'Sink (datahub-rest) report:\n'
           "{'records_written': 0,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': None,\n"
           " 'downstream_end_time': None,\n"
           " 'downstream_total_latency_in_seconds': None,\n"
           " 'gms_version': 'v0.8.32'}\n"
           '\n'
           'Pipeline finished with warnings\n',
           "2022-04-07 09:33:04.355649 [exec_id=1d74021a-b9db-4d85-9e74-38a099c4be7a] INFO: Successfully executed 'datahub ingest'"]}
Execution finished successfully!
I tried to exclude them smth like
Copy code
user_name_pattern:
            deny: '^[^\s]{4}$'
but it didn’t work and job failed
Copy code
'[2022-04-07 10:08:23,813] INFO     {datahub.cli.ingest_cli:88} - DataHub CLI version: 0.8.32\n'
           '[2022-04-07 10:08:23,857] INFO     {datahub.ingestion.sink.datahub_rest:60} - Setting gms config\n'
           '[2022-04-07 10:08:25,438] INFO     {datahub.ingestion.source_config.sql.snowflake:113} - using authenticator type '
           "'DEFAULT_AUTHENTICATOR'\n"
           '1 validation error for SnowflakeUsageConfig\n'
           'user_name_pattern\n'
           '  extra fields not permitted (type=value_error.extra)\n',
           "2022-04-07 10:08:26.083223 [exec_id=526b209f-8ac4-46f5-8d7f-93434ef3b335] INFO: Failed to execute 'datahub ingest'",
           '2022-04-07 10:08:26.084180 [exec_id=526b209f-8ac4-46f5-8d7f-93434ef3b335] INFO: Caught exception EXECUTING '
           'task_id=526b209f-8ac4-46f5-8d7f-93434ef3b335, name=RUN_INGEST, stacktrace=Traceback (most recent call last):\n'
           '  File "/usr/local/lib/python3.9/site-packages/acryl/executor/execution/default_executor.py", line 119, in execute_task\n'
           '    self.event_loop.run_until_complete(task_future)\n'
           '  File "/usr/local/lib/python3.9/site-packages/nest_asyncio.py", line 81, in run_until_complete\n'
           '    return f.result()\n'
           '  File "/usr/local/lib/python3.9/asyncio/futures.py", line 201, in result\n'
           '    raise self._exception\n'
           '  File "/usr/local/lib/python3.9/asyncio/tasks.py", line 256, in __step\n'
           '    result = coro.send(None)\n'
           '  File "/usr/local/lib/python3.9/site-packages/acryl/executor/execution/sub_process_ingestion_task.py", line 115, in execute\n'
           '    raise TaskError("Failed to execute \'datahub ingest\'")\n'
           "acryl.executor.execution.task.TaskError: Failed to execute 'datahub ingest'\n"]}
s
Can we please clarify the problem with 
snowflake
source first? Is that working? After that is done then we can talk about 
snowflake-usage
source
👍 1
c
sure, checking it
In the source report there would be list of roles under 
role_grants
. You can see it to verify whether you have it or not
I didn’t find 
role_grants
in the job logs (i.e. logs in UI for the job). Checking permissions in SF
we’ve just executed
Copy code
grant imported privileges on database snowflake to role YOUR_ROLE;
I restarted the jobs
snowflake-usage
job finished, no changes there. Still the same issue with empty emails. 
snowflake
metadata ingestion is still running. I will take about 1.5h according to the previous runs
s
We will wait for 
snowflake
source to see if it produces lineage now Regarding 
snowflake-usage
were there no 
workunit_ids
at all in the most recent run in the logs at all?
Can you please add 
email_domain
in your 
snowflake-usage
recipe? It is mentioned in https://datahubproject.io/docs/metadata-ingestion/source_docs/snowflake/#quickstart-recipe-1
c
I didn’t find any entry of 
workunit_ids
in the last log for snowflake-usage job.
Can you please add 
email_domain
in your 
snowflake-usage
recipe?
sure, will try now
got it for snowflake-usage when set email_domain
Copy code
'[2022-04-07 15:07:22,345] INFO     {datahub.ingestion.source.usage.snowflake_usage:434} - Getting usage history\n'
           '[2022-04-07 15:08:40,310] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"DATABASE_NAME"\']\n'
           '[2022-04-07 15:08:40,310] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"DATABASE_NAME"\']\n'
           '[2022-04-07 15:08:41,447] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"DATABASE_NAME"\']\n'
           '[2022-04-07 15:08:41,447] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"DATABASE_NAME"\']\n'
           '[2022-04-07 15:08:41,524] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"DATABASE_NAME"\']\n'
           '[2022-04-07 15:08:41,524] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"DATABASE_NAME"\']\n'
           '[2022-04-07 15:08:41,531] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"DATABASE_NAME"\']\n'
...
'Source (snowflake-usage) report:\n'
           "{'workunits_produced': 0,\n"
           " 'workunit_ids': [],\n"
I wonder if I need to change
Copy code
ignoreCase: false
        schema_pattern:
            ignoreCase: false
s
Can you please give an example of the 
DATABASE_NAME
with alphabets changed? That error happens in case there are 4 
.
in the 
DATABASE_NAME
c
sure, it’s similar to this 
MIP_CounterData
(case and special symbols are the same)
it was set like that
Copy code
database_pattern:
            allow:
                - ^MIP_CounterData$
            ignoreCase: false
        schema_pattern:
            ignoreCase: false
s
Do you have 
min_access_history_time
, 
max_access_history_time
in the source report? You are using Enterprise edition of snowflake, right?
c
yes, that’s correct
Copy code
" 'failures': {},\n"
           " 'cli_version': '0.8.32',\n"
           " 'cli_entry_location': '/tmp/datahub/ingest/venv-67734618-8c21-45bf-b85a-07c9372bb0a3/lib/python3.9/site-packages/datahub/__init__.py',\n"
           " 'py_version': '3.9.9 (main, Dec 21 2021, 10:03:34) \\n[GCC 10.2.1 20210110]',\n"
           " 'py_exec_path': '/tmp/datahub/ingest/venv-67734618-8c21-45bf-b85a-07c9372bb0a3/bin/python3',\n"
           " 'os_details': 'Linux-5.4.176-91.338.amzn2.x86_64-x86_64-with-glibc2.31',\n"
           " 'end_time': datetime.datetime(2022, 4, 8, 0, 0, tzinfo=datetime.timezone.utc),\n"
           " 'start_time': datetime.datetime(2022, 4, 6, 0, 0, tzinfo=datetime.timezone.utc),\n"
           " 'min_access_history_time': datetime.datetime(2021, 4, 1, 0, 0, 36, 883000, tzinfo=datetime.timezone.utc),\n"
           " 'max_access_history_time': datetime.datetime(2022, 4, 7, 15, 6, 3, 864000, tzinfo=datetime.timezone.utc),\n"
           " 'access_history_range_query_secs': 43.63,\n"
           " 'access_history_query_secs': 74.22,\n"
           " 'rows_processed': 209112,\n"
           " 'rows_missing_query_text': 109,\n"
           " 'rows_zero_base_objects_accessed': 209003,\n"
           " 'rows_zero_direct_objects_accessed': 209003,\n"
           " 'rows_missing_email': 0,\n"
           " 'rows_parsing_error': 0}\n"
           'Sink (datahub-rest) report:\n'
           "{'records_written': 0,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': None,\n"
           " 'downstream_end_time': None,\n"
           " 'downstream_total_latency_in_seconds': None,\n"
           " 'gms_version': 'v0.8.32'}\n"
           '\n'
           'Pipeline finished with warnings\n',
           "2022-04-07 15:09:04.480230 [exec_id=67734618-8c21-45bf-b85a-07c9372bb0a3] INFO: Successfully executed 'datahub ingest'"]}
Execution finished successfully!
this is from the pre-last run ^^^ (now I started it again without database_pattern)
s
The above one shows why you don't have usage
Copy code
rows_processed - rows_missing_query_text - rows_zero_base_objects_accessed == 0
You will have to make the start_time to be earlier for 
snowflake-usage
c
could you help with the format of the date in the recipe? Should it be smth like this?
Copy code
start_time: "1647358289"
1647358289
is March 15, 2022
s
Copy code
start_time: "2022-03-15T00:00:00Z"
Did this work out @cold-hydrogen-10513?
c
hi Aseem, unfortunately, it failed. the recipe is the following
Copy code
source:
    type: snowflake-usage
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        username: '${SNOWFLAKE_USER}'
        password: '${SNOWFLAKE_PASS}'
        env: NON_PROD
        format_sql_queries: true
        email_domain: <http://company_domain.co.us|company_domain.co.us>
        start_time: '2022-03-01T00:00:00Z'
        database_pattern:
            allow:
                - ^MIP_CounterData$
            ignoreCase: false
        schema_pattern:
            ignoreCase: false
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
I’m attaching the file with logs
sf-usage-log.log
the metadata ingestion for sf has finished successfully. But I checked the lineage for the tables and it’s still not present. I’m attaching the log with files (I had to cut some of the lines logs where more tables for the same DB are listed. Also I renamed our tables)
Copy code
source:
    type: snowflake
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        username: '${SNOWFLAKE_USER}'
        password: '${SNOWFLAKE_PASS}'
        warehouse: SDP_NONPROD_TEST
        options:
            case_sensitive: true
        include_views: true
        include_tables: true
        env: NON_PROD
        report_upstream_lineage: true
        database_pattern:
            allow:
                - ^MIP_CounterData$
            ignoreCase: false
        schema_pattern:
            allow:
                - ^MortOut$
                - ^SellPres$
                - ^MIP$
                - ^MIPPre$
            deny:
                - ^HiMipInstanceOne$
                - ^HiMipInstanceTwo$
            ignoreCase: false
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
sf-metadata-ingest.log
s
Hi @cold-hydrogen-10513 Regarding the 
snowflake-usage
source it is being killed. That usually means an Out of memory error. Can you please increase the memory of the actions pod and try this again? I have an open PR https://github.com/datahub-project/datahub/pull/4595 which should help lower the memory usage for this. I will coordinate with @helpful-optician-78938 and try to get it merged soon. The source/sink reports are not being published due to the error. I have an another PR https://github.com/datahub-project/datahub/pull/4592 which should help with that. I will coordinate with @helpful-optician-78938 and try to get it merged soon.
Hi @cold-hydrogen-10513 Regarding the 
snowflake
source can you please try adding 
ignore_start_time_lineage: true
in the recipe and run it once again? Also, there must be an 
upstream_lineage
and 
cli_version
in the source report. Request you to not remove them or send the logs over DMs.
c
hi @square-activity-64562
Can you please increase the memory of the actions pod and try this again?
thanks, I’ll try that
Regarding the 
snowflake
source can you please try adding 
ignore_start_time_lineage: true
in the recipe and run it once again? Also, there must be an 
upstream_lineage
and 
cli_version
in the source report. Request you to not remove them or send the logs over DMs.
sure, will do now, thanks
hi @square-activity-64562 
snowflake-usage
the job finished successfully but I didn’t notice any usage data in datahub UI. We have about 1.5k datasets so I wonder if all of them should have a usage statistics as a result of that job. Is there a way to check that we have the usage statistics without checking all the tables one by one? 
snowflake
has been running from yesterday (about 20h for now). The config is the following. Do you think it’s worth waiting or smth went wrong already?
Copy code
source:
    type: snowflake
    config:
        host_port: <http://company_name.us-east-1.snowflakecomputing.com|company_name.us-east-1.snowflakecomputing.com>
        username: '${SNOWFLAKE_USER}'
        password: '${SNOWFLAKE_PASS}'
        warehouse: NONPROD_VWH
        options:
            case_sensitive: true
        include_views: true
        include_tables: true
        env: NON_PROD
        report_upstream_lineage: true
        ignore_start_time_lineage: true
        database_pattern:
            allow:
                - ^MIP_CounterData$
            ignoreCase: false
        schema_pattern:
            allow:
                - ^DataOutbound$
                - ^DataAudit$
            deny:
                - ^DataToExcludeWP$
            ignoreCase: false
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-gms.datahub.svc.cluster.local:8080>'
s
Regarding 
snowflake
I would suggest stopping the ingestion, remove 
ignore_start_time_lineage
and try to add 
start_time: '2022-03-01T00:00:00Z'
and re-run it
Regarding 
snowflake-usage
you must have seen 
workunit_ids
in the logs, right? Their names should give you some idea about which might have usage information
c
Regarding 
snowflake-usage
you must have seen 
workunit_ids
in the logs, right? Their names should give you some idea about which might have usage information
thanks, looks like it didn’t import anything. I’m sending the log below
Copy code
.....................................................
 '[2022-04-12 14:00:51,481] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"MIP_CounterData"\']\n'
           '[2022-04-12 14:00:51,481] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"MIP_CounterData"\']\n'
           '[2022-04-12 14:00:51,481] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"MIP_CounterData"\']\n'
           '[2022-04-12 14:00:51,481] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"MIP_CounterData"\']\n'
           '[2022-04-12 14:00:51,481] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"MIP_CounterData"\']\n'
           '[2022-04-12 14:00:51,481] WARNING  {datahub.ingestion.source.usage.snowflake_usage:93} - invalid-dataset-pattern => Found '
           '[\'"MIP_CounterData"\']\n'
           '[2022-04-12 14:00:57,346] INFO     {datahub.cli.ingest_cli:106} - Finished metadata ingestion\n'
           '\n'
           'Source (snowflake-usage) report:\n'
           "{'workunits_produced': 0,\n"
           " 'workunit_ids': [],\n"
           ' \'warnings\': {\'invalid-dataset-pattern\': [\'Found [\\\'"MIP_CounterData"\\\']\',\n'
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\',\n'
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\',\n'
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\',\n'
  .....................................................
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\',\n'
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\',\n'
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\',\n'
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\',\n'
           '                                          \'Found [\\\'"MIP_CounterData"\\\']\']},\n'
           " 'failures': {},\n"
           " 'cli_version': '0.8.32',\n"
           " 'cli_entry_location': '/tmp/datahub/ingest/venv-c3bba36e-d150-43fd-91a8-c4fab349e170/lib/python3.9/site-packages/datahub/__init__.py',\n"
           " 'py_version': '3.9.9 (main, Dec 21 2021, 10:03:34) \\n[GCC 10.2.1 20210110]',\n"
           " 'py_exec_path': '/tmp/datahub/ingest/venv-c3bba36e-d150-43fd-91a8-c4fab349e170/bin/python3',\n"
           " 'os_details': 'Linux-5.4.176-91.338.amzn2.x86_64-x86_64-with-glibc2.31',\n"
           " 'end_time': datetime.datetime(2022, 4, 13, 0, 0, tzinfo=datetime.timezone.utc),\n"
           " 'start_time': datetime.datetime(2022, 3, 1, 0, 0, tzinfo=datetime.timezone.utc),\n"
           " 'min_access_history_time': datetime.datetime(2021, 4, 1, 0, 0, 36, 883000, tzinfo=datetime.timezone.utc),\n"
           " 'max_access_history_time': datetime.datetime(2022, 4, 12, 13, 51, 17, 676000, tzinfo=datetime.timezone.utc),\n"
           " 'access_history_range_query_secs': 49.77,\n"
           " 'access_history_query_secs': 128.57,\n"
           " 'rows_processed': 2944151,\n"
           " 'rows_missing_query_text': 253,\n"
           " 'rows_zero_base_objects_accessed': 2943898,\n"
           " 'rows_zero_direct_objects_accessed': 2943898,\n"
           " 'rows_missing_email': 0,\n"
           " 'rows_parsing_error': 0}\n"
           'Sink (datahub-rest) report:\n'
           "{'records_written': 0,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': None,\n"
           " 'downstream_end_time': None,\n"
           " 'downstream_total_latency_in_seconds': None,\n"
           " 'gms_version': 'v0.8.32'}\n"
           '\n'
           'Pipeline finished with warnings\n',
           "2022-04-12 14:00:58.117506 [exec_id=c3bba36e-d150-43fd-91a8-c4fab349e170] INFO: Successfully executed 'datahub ingest'"]}
Execution finished successfully!
Regarding 
snowflake
I would suggest stopping the ingestion, remove 
ignore_start_time_lineage
and try to add 
start_time: '2022-03-01T00:00:00Z'
and re-run it
thanks, will try now
I set start_time to 2022-02-01 for 
snowflake
and now see 47 records written in the log
Copy code
'Sink (datahub-rest) report:\n'
           "{'records_written': 47,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': datetime.datetime(2022, 4, 13, 12, 54, 39, 828688),\n"
           " 'downstream_end_time': datetime.datetime(2022, 4, 13, 12, 55, 6, 862516),\n"
           " 'downstream_total_latency_in_seconds': 27.033828,\n"
           " 'gms_version': 'v0.8.32'}\n"
I don’t know how to find them though… I have a lot of datasets (9.6k) after multiple experiments and it’s hard to determine what exactly were processed in this run. Is there a proper way to cleanup the datasets?
s
The source report should have 
workunit_ids
which should help you out in finding what was written
c
unfortunately 
workunit_ids
is not present in the source report of 
snowflake
job. It can be found only in 
snowflake-usage
s
Can you please try with latest CLI which was released yesterday? https://github.com/acryldata/datahub/releases/tag/v0.8.32.5 That has a fix which is supposed to ensure that source and sink reports are shown always https://datahubproject.io/docs/ui-ingestion/#advanced-running-with-a-specific-cli-version shows how to use a specific version
c
thanks, will try now. Could you please tell me is there a safe/proper way to cleanup the datasets in datahub? I.e. the resources that I’ve already ingested. I thought about truncating the tables in datahub MySQL database but I’m not sure that that’s fine.
s
https://datahubproject.io/docs/how/delete-metadata/ Top right search in the docs is usually able to get answers
@cold-hydrogen-10513 Did this work out for you?
c
hi Aseem, unfortunately, it didn’t work. I was mainly concentrated on 
snowflake
type and didn’t do a lot of checks for 
snowflake-usage
. From the last results it still doesn’t report the lineage, the job runs 5070.024s. I set 
start_time: '2022-02-01T00:00:00Z'
, changed schema pattern to the following format
Copy code
options:
            case_sensitive: true
        include_views: true
        include_tables: true
        env: NON_PROD
        report_upstream_lineage: true
        start_time: '2022-02-01T00:00:00Z'
        schema_pattern:
            allow:
                - 'MySchemaName.*'
and database
Copy code
database_pattern:
            allow:
                - ^MIP_CounterData$
Before running I cleared all the ingested metadata from previous runs. After this job finished only table column names were imported and lineage was still empty. I started working on metadata ingestion using a DAG https://github.com/datahub-project/datahub/blob/master/metadata-ingestion/src/datahub_provider/example_dags/generic_recipe_sample_dag.py and faced an issue that 
snowflake is disabled; try running: pip install 'acryl-datahub[snowflake]'
although I have
Copy code
acryl-datahub==0.8.32.7
acryl-datahub[snowflake]
in my requirements.txt
s
Can you attempt to add these to your DAG inside the operator to validate what is installed where? We sometimes have seen multiple python versions on people's machines leading to this behaviour.
Copy code
import sys
print(sys.version)
print(sys.executable)

import datahub
print(datahub.__file__)
print(datahub.__version__)

from datahub.ingestion.source.source_registry import source_registry
print(source_registry.summary(verbose=verbose))

import airflow.version
print(airflow.version.version)
Also, you are using Snowflake Enterprise edition, correct?
Is it still showing the same problem of 
invalid-dataset-pattern
?
c
Also, you are using Snowflake Enterprise edition, correct?
we’re using Business Critical Edition
👍 1
Is it still showing the same problem of 
invalid-dataset-pattern
?
I think this one was for 
snowflake-usage
which I didn’t check yet. Our main goal now is to have lineage which is the feature of 
snowflake
type. I’ll try to check usage later
Can you attempt to add these to your DAG inside the operator to validate what is installed where? We sometimes have seen multiple python versions on people’s machines leading to this behaviour.
yeah, will try now, thanks
s
Not an issue. We can focus on 
snowflake
to get the lineage. The 
role
defaults to 
datahub_role
for that user?
There should be a 
role_grants
in the 
snowflake
source report. Do you see 
snowflake
database name in there? That will help validate that the user has access to 
snowflake
database which has history which is used for creating the lineage
Just because 
datahub_role
has been granted it does not mean the user will use that by default. The default could be a different role.
c
default role was set to the one that we created for datahub
I’ll check the source report
looks like this parameter doesn’t exist
Copy code
print(source_registry.summary(verbose=verbose))
Copy code
Broken DAG: [/usr/local/airflow/dags/datahub_metadata_ingest/main.py] name 'verbose' is not defined
s
verbose=True
It should have been this ^
c
had to comment out
Copy code
from datahub.ingestion.source.source_registry import source_registry
print(source_registry.summary(verbose=True))
because it leads to this issue 
Broken DAG: [/usr/local/airflow/dags/datahub_metadata_ingest/main.py] module 'google.protobuf.descriptor' has no attribute '_internal_create_key'
results of debug
Copy code
sys.executable /usr/bin/python3.7
sys.version 3.7.10 (default, Mar  2 2021, 09:06:08) 
[GCC 8.3.0]
datahub.__file__ /usr/lib/python3.7/site-packages/datahub/__init__.py
datahub.__version__ 0.8.33
airflow.version.version 1.10.12+astro.2
@square-activity-64562 I noticed that
Copy code
The Airflow lineage backend is only supported in Airflow 1.10.15+ and 2.0.2+.
but we’re using 
1.10.12
. As I understand it, Airflow lineage won’t work for now but Snowflake lineage ingestion that is imported by a DAG in Airflow 
1.10.12
should work. Is this correct? https://datahubproject.io/docs/lineage/airflow
s
It should. But our tests run only for Python 3.6 and 3.9. So there could be something that is missing. I did not expect 
Broken DAG: [/usr/local/airflow/dags/datahub_metadata_ingest/main.py] module 'google.protobuf.descriptor' has no attribute '_internal_create_key'
to happen. That seems problematic. You mentioned earlier
I started working on metadata ingestion using a DAG
https://github.com/datahub-project/datahub/blob/master/metadata-ingestion/src/datahub_provider/example_dags/generic_recipe_sample_dag.py and faced an issue that
snowflake is disabled; try running: pip install 'acryl-datahub[snowflake]'
although I have
```acryl-datahub==0.8.32.7
acryl-datahub[snowflake]```
in my requirements.txt
Few questions • How did you check that this is actually installed? The version in your requirements.txt and the one you mentioned are different. • Any particular reason why did you switch to using a DAG instead of the UI based ingestion? • There should be a 
role_grants
in the 
snowflake
source report. Do you see 
snowflake
database name in there? That will help validate that the user has access to 
snowflake
database which has history which is used for creating the lineage
c
hi @square-activity-64562
• How did you check that this is actually installed? The version in your requirements.txt and the one you mentioned are different.
versions can be different because I was changing them all the time during the debugging.
• Any particular reason why did you switch to using a DAG instead of the UI based ingestion?
yes, UI based took more than 1h and it was hard to understand why that happened. I assume that some log messages are suppressed in UI based ingestion. Also all the options that I tried didn’t help to get lineage.
• There should be a 
role_grants
in the 
snowflake
source report. Do you see 
snowflake
database name in there? That will help validate that the user has access to 
snowflake
database which has history which is used for creating the lineage
I’ve just checked two last reports for the last runs and they don’t contain 
role_grants
field. I’m still configuring the DAG for datahub ingestion and yesterday I managed to run it. I think I found the root cause why ingestion runs so long for us. Looks like datahub checks the permissions of its role at the beginning. In our case it takes more than 1h https://github.com/datahub-project/datahub/blob/master/metadata-ingestion/src/datahub/ingestion/source/sql/snowflake.py#L110
Copy code
show grants to role <ROLE_NAME>
Could you please tell me is this check required? Is datahub uses those permissions to create a list of databases and schemas which can be scanned by datahub?
s
@cold-hydrogen-10513 This was added mainly for debugging purposes. I can send a PR to add an option to not check the grants.
raised PR to disable this by default https://github.com/datahub-project/datahub/pull/4760
c
thanks!
hi Aseem, could you please tell me how often are releases triggered? I see that the last one doesn’t contain your changes from the MR above https://pypi.org/project/acryl-datahub/#history
s
The next CLI release should be going out on early next week. My PR was merged in today only so that did not get into last merge which happened a few hours before that.
thank you 1
c
hi Aseem, I’m trying to understand why the lineage is not imported. Is it possible that we just don’t have this data in Snowflake? The only data that I see in datahub now is the list of tables from one schema that I set in a schema pattern field in recipe. For each table there is only list of columns and lineage tab is not active. Here’s the log from my DAG
Copy code
[2022-05-04 13:35:00,979] {{cursor.py:705}} INFO - query: [WITH table_lineage_history AS ( SELECT r.value:"objectName" AS upstream_table_na...]
[2022-05-04 13:35:55,916] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:35:59,234] {{cursor.py:705}} INFO - query: [ROLLBACK]
[2022-05-04 13:35:59,294] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:35:59,312] {{snowflake.py:450}} INFO - A total of 0 Table->Table edges found for 0 downstream tables.
[2022-05-04 13:35:59,327] {{connection.py:262}} INFO - Snowflake Connector for Python Version: 2.7.7, Python Version: 3.7.10, Platform: Linux-4.14.275-207.503.amzn2.x86_64-x86_64-with-glibc2.2.5
[2022-05-04 13:35:59,345] {{connection.py:874}} INFO - This connection is in OCSP Fail Open Mode. TLS Certificates would be checked for validity and revocation status. Any other Certificate Revocation related exceptions or OCSP Responder failures would be disregarded in favor of connectivity.
[2022-05-04 13:35:59,776] {{cursor.py:705}} INFO - query: [SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1]
[2022-05-04 13:35:59,840] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:35:59,859] {{cursor.py:705}} INFO - query: [SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1]
[2022-05-04 13:35:59,937] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:35:59,957] {{cursor.py:705}} INFO - query: [ROLLBACK]
[2022-05-04 13:36:00,022] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:36:00,038] {{cursor.py:705}} INFO - query: [SELECT concat( referenced_database, '.', referenced_schema, '.', referenced_obje...]
[2022-05-04 13:36:16,627] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:36:20,986] {{cursor.py:705}} INFO - query: [ROLLBACK]
[2022-05-04 13:36:21,071] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:36:21,088] {{snowflake.py:215}} INFO - A total of 0 View upstream edges found.
[2022-05-04 13:36:21,103] {{cursor.py:705}} INFO - query: [WITH view_lineage_history AS ( SELECT vu.value : "objectName" AS view_name, vu.v...]
[2022-05-04 13:37:14,140] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:37:16,212] {{cursor.py:705}} INFO - query: [ROLLBACK]
[2022-05-04 13:37:16,276] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:37:16,294] {{snowflake.py:309}} INFO - Found 0 View->Table edges.
[2022-05-04 13:37:16,310] {{connection.py:262}} INFO - Snowflake Connector for Python Version: 2.7.7, Python Version: 3.7.10, Platform: Linux-4.14.275-207.503.amzn2.x86_64-x86_64-with-glibc2.2.5
[2022-05-04 13:37:16,331] {{connection.py:874}} INFO - This connection is in OCSP Fail Open Mode. TLS Certificates would be checked for validity and revocation status. Any other Certificate Revocation related exceptions or OCSP Responder failures would be disregarded in favor of connectivity.
[2022-05-04 13:37:16,805] {{cursor.py:705}} INFO - query: [SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1]
[2022-05-04 13:37:16,869] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:37:16,897] {{cursor.py:705}} INFO - query: [SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1]
[2022-05-04 13:37:16,963] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:37:16,984] {{cursor.py:705}} INFO - query: [ROLLBACK]
[2022-05-04 13:37:17,054] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:37:17,074] {{cursor.py:705}} INFO - query: [WITH external_table_lineage_history AS ( SELECT r.value:"locations" as upstream_...]
[2022-05-04 13:38:09,258] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:38:09,329] {{cursor.py:705}} INFO - query: [ROLLBACK]
[2022-05-04 13:38:09,400] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:38:09,422] {{cursor.py:705}} INFO - query: [show external tables in account]
[2022-05-04 13:38:12,880] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:38:12,901] {{cursor.py:705}} INFO - query: [ROLLBACK]
[2022-05-04 13:38:12,961] {{cursor.py:729}} INFO - query execution done
[2022-05-04 13:38:12,981] {{snowflake.py:388}} INFO - Found 0 external lineage edges.
If I run the recipe for the whole database the result is the same. I.e. there are only four occurrences of 
Found 0 external lineage edges.
and lineage is not exported.
Copy code
{
  "recipe": {
    "source": {
      "type": "snowflake",
      "config": {
        "account_id": "<http://companyname.us-east-1.com|companyname.us-east-1.com>",
        "username": "USER",
        "password": "PASS",
        "role": "METADATA__ROLE",
        "options": {
          "case_sensitive": [
            true
          ]
        },
        "include_table_lineage": true,
        "include_view_lineage": true,
        "include_views": true,
        "include_tables": true,
        "ignore_start_time_lineage": true,
        "env": "NON_PROD",
        "database_pattern": {
          "allow": [
            "^MIP_CounterData$"
          ],
          "ignoreCase": false
        }
      }
    },
    "sink": {
      "type": "datahub-rest",
      "config": {
        "server": "<http://datahub-gms.domain.com>"
      }
    }
  }
}
if I run the query for lineage it gives me about 1700 rows but when I go to one of the tables which is returned in 
UPSTREAM_TABLE_NAME
column I don’t see lineage for it
Copy code
WITH table_lineage_history AS (
    SELECT
        r.value:"objectName" AS upstream_table_name,
        r.value:"objectDomain" AS upstream_table_domain,
        r.value:"columns" AS upstream_table_columns,
        w.value:"objectName" AS downstream_table_name,
        w.value:"objectDomain" AS downstream_table_domain,
        w.value:"columns" AS downstream_table_columns,
        t.query_start_time AS query_start_time
    FROM
        (SELECT * from snowflake.account_usage.access_history) t,
        lateral flatten(input => t.DIRECT_OBJECTS_ACCESSED) r,
        lateral flatten(input => t.OBJECTS_MODIFIED) w
    WHERE r.value:"objectId" IS NOT NULL
    AND w.value:"objectId" IS NOT NULL
    AND w.value:"objectName" NOT LIKE '%.GE_TMP_%'
    AND w.value:"objectName" NOT LIKE '%.GE_TEMP_%'
    AND w.value:"objectName" LIKE '%MIP_CounterData%'
    AND t.query_start_time >= to_timestamp_ltz(0, 3)
    AND t.query_start_time < to_timestamp_ltz(1651708800000, 3))
SELECT upstream_table_name, downstream_table_name, upstream_table_columns, downstream_table_columns
FROM table_lineage_history
WHERE upstream_table_domain in ('Table', 'External table') and downstream_table_domain = 'Table'
QUALIFY ROW_NUMBER() OVER (PARTITION BY downstream_table_name, upstream_table_name ORDER BY query_start_time DESC) = 1;
s
Do you have external tables? If yes, there was a bug in the connector where if you had a default schema/database for the user with which the ingestion was running it would only get for the default namespace. You can try with the latest connector which has the required bugfix.
For the views it depends on when the view was created. Can you try creating a new view in a separate database and then running ingestion for that database only? Will need to wait 45 min to 3 hours (snowflake latency) for the information to be present in the relevant tables.
c
You can try with the latest connector which has the required bugfix.
got it, thanks. Should I use this version to get this fix 
pip install acryl-datahub==0.8.34.2rc2
?
For the views it depends on when the view was created. Can you try creating a new view in a separate database and then running ingestion for that database only? Will need to wait 45 min to 3 hours (snowflake latency) for the information to be present in the relevant tables.
most of those that I work with were created more than a month ago.
Can you try creating a new view in a separate database and then running ingestion for that database only?
thanks, I’ll ask our SF admins
s
Not the rc releases. The one that is stable before that
c
oh, I already use 
0.8.34.1
s
Hi @cold-hydrogen-10513 Did the new view in separate database thing work out?
4 Views
Open in Slack
PreviousNext
Powered by