Secoda Community

image.png

Hey <@U031Z4XUNMD> I’d love to hear how your team is pulling this lineage information from the Power BI API. We could apply that to our extractors to get the end-to-end lineage going for you.

When we made the PowerBI integration I couldn’t find any information related to the connections between datasets and the source tables from snowflake. If we have that information it’s very straightforward to add it to the Secoda product.

Hi <@U01MHQR7DE2>, so we're using the Power BI Admin API's Metadata Scanning feature. This does have to be enabled by the Power BI admin to start collecting metadata.

<https://docs.microsoft.com/en-us/power-bi/admin/service-admin-metadata-scanning>

These scan results include a "tables" object inside of a dataset object, which includes table names and a "source" expression that provides the snowflake connection details, database, schema, and table name.

The "source" expression is the raw Power Query

This is helpful context, I did see they recently released the scanner API and haven’t gotten the chance to explore it further. So it’s good that you’ve actually accomplished getting lineage information so that makes me optimistic we can do it as well.

Is this a known issue? <@U01MHQR7DE2> I'll send you the screenshots directly

You’re correct Amit, this is a bug. We’ll update the Postgres extraction to pull in the materialized views <https://linear.app/secoda/issue/SEC-316/postgres-materialized-views-missing>

This is currently not available, but I’ve added it to our roadmap. It’s a fairly straightforward feature to implement so I’ll let you know when it’s been prioritized.

Is this what you’re referring to Richard? <https://www.loom.com/share/5f414a33189145db91c6b8910294f13e>

kind of. I didnt think of this so I appreciate you showing me. What I had in mind is more akin to what you would see on a github readme where the link would auto-scroll down to a section not open a new tab

So if you copy the “Share” link for a tab it will auto scroll to that section like the GitHub markdown links.

I’ll take a look into this Ryan. Thanks for letting me know. I’ll let you know when I have an answer.

Can you let me know if the numbers are more accurate now Ryan. It looks like there was a bug on our end where we were counting all of the resources including the ones from unchecked schemas.

There’s still an outstanding bug where filters are currently broken for dashboards. But it’s being looked into today.

It's closer now (22 homepage - 2 search page for published, 307 homepage - 267 search page for draft). But sounds good, thanks!

Okay, I’ll take a look closer into what’s causing the discrepancy

I’ll take a look into this right now, should have an update in a couple of minutes.

<@U031Z4XUNMD> I’ve fixed the issue, you should just see the single Snowflake option now

Hey Ryan, do you know if you have all of your environments/jobs associated with `analytics_staging` selected? You can find those in the “Groups” tab under the dbt integration.

For the second point, we don’t currently remove the schemas after they’ve been imported, but what we can do is manually delete them if you provide us with the names that the user doesn’t have access to.

Hey Andrew, the dbt integration is now showing both analytics and analytics_staging! For the snowflake integration, Secoda should have access to every schema in the RAW database and only "analytics" and "analytics_staging" in the ANALYTICS database. Thanks!

Where if I look at the Secoda web, the display show much more information

Hi Iqbal, the websites images morning need to be updated with the latest version of the app. Are there are any particular fields you’d want to see?

Hi <@U01MHQR7DE2>. I think what we have in the website is pretty much what I want to see and also it will be nice if it's also appears in default information in Dictionary. Because for now I use custom properties to make field like Status and Related table.

You can add related tables to a dictionary terms by tagging a dictionary term in the table documentation using the @ symbol

Does it appear automatically after tagging the dictionary in table documentation? <@U01PM6182AX>

That’s also a pending issue that we have prioritized this week. Normally it shows up in the list of properties as “Related Resources” as seen in the screenshot on the website.

Here’s the issue so you can follow it <@U032EFBHWBD> <https://linear.app/secoda/issue/SEC-281/related-resources-on-dictionary-term-broken>

Do you have a screenshot of how it looks on your end <@U033ER26CPN>?

this is not representative of other screens but just to make the point. (not sure what I can share and not share when it comes to my companies dashboards so trying to play it safe)

but you see the white space beneath the embeded object. It would be beneficial to expand this to occupy that white space.

Makes sense. We didn't add the ability to expand the size. That's good feedback that we can try to incorporate into a future release :slightly_smiling_face: cc. <@U02GZEAV18D>

The tooltip is a quick fix we can push, that’s a good suggestion Ryan.

What do you mean that lineage stuff should be linked? If you click on the table it should bring you to that table’s page. If it’s not doing that it’s a bug on our end.

Ah, looks like it’s a bug on our end. I’ll fix that right now.

Awesome, thanks! Also, clicking these tables in the tabular view (upstream/downstream) doesn't work either

Hey <@U031Z4XUNMD> we’ve fixed the links in the lineage graph and upstream/downstream tables. We’ve also added tooltips with the full table name on the lineage nodes.

Looks good! One more minor detail that relates to this would be a tooltip on the tabular view in upstream/downstream as well! Thanks!

We’ll get that tooltip in there, thanks for the feedback!

Hi Borja, so you can test out Secoda on <https://app.secoda.co>.

In terms of k8s it’s not something we currently support, but we do support deploying Secoda onprem with ECS and Terraform. Is k8s a must have or are you open to using ECS — AWS’s managed k8s?

Yes, we can use ECS in our system, where is posible to get the terraform templates to test it? thanks for answer this fast.

Here is the documentation for the deployment. We only provide access to the on-premise installation for customers completing a POC since it does require some additional customer support on our end. <https://secoda.notion.site/Terraform-48169ebc285e466d9aa3383deca9b1f7>

If you wanted to chat with Etai about on-premise options you can book a time in his Calendar using this link <https://calendly.com/etai-secoda/rolling30> cc <@U01PM6182AX>

image (4).png

Hey <@U02H1V13V99> can you check if they’re back now?

Thanks <@U01MHQR7DE2> , I can see them now. Can you explain what was the issue before ?

It looks like our elasticsearch index updater ran into an issue which caused this problem. I’ve added an issue to look further into this issue <https://linear.app/secoda/issue/SEC-357/search-index-not-updating-properly>

Hey <@U033ER26CPN> can you let me know if it’s using the proper user now.

It is! thank you. Is this something you did behind the scenes using tools you only have access to or is this something we could of fixed?

It’s an issue that we need to patch. The integration params are in two separate DBs right now because we are slowly migrating from Neo4j -&gt; Postgres. And so we have a special endpoint we’ve created to sync things up if they get out of sync like they did in your case.

when I export I get what I would expect but its doesn't show up in the document as a table

What looks to be happening is we’re only returning the result of the first query `set park_of_interest = 16;`  whereas we should be returning the last query result. cc <@U02GZEAV18D>

<https://linear.app/secoda/issue/SEC-360/return-last-query-execution-results>

thanks. fyi: <@U031Z4XUNMD> <@U033EBH4U59> <@U0339JFMEUD>

Do you know if the user you have connected to Secoda has permission to view these tables? I know in the past <@U02K8BY6RCK> ran into this issue and he had to give permission to the Secoda user which resolved it.

As an admin, I don’t see the tables, so I should have permissions to see all tables no?

I’m talking specifically about permissions in Postgres. We run this query behind the scenes, and I imagine it’s not getting picked up with the Postgres user in Secoda.
```        SELECT 
          current_database() AS cluster,
          c.table_schema AS schema,
          c.table_name AS name,
          pgtd.description AS description,
          c.column_name AS col_name,
          c.data_type AS col_type,
          pgcd.description AS col_description,
          c.ordinal_position AS col_sort_order,
          t.table_type = 'VIEW' as is_view,
          fks.reference_tbl,
          fks.reference_col,
          pks.column_name as pk_column_name
        FROM information_schema.columns c
        LEFT JOIN pg_catalog.pg_statio_all_tables st 
          ON c.table_schema = st.schemaname 
          AND c.table_name = st.relname
        LEFT JOIN pg_catalog.pg_description pgtd
          ON pgtd.objoid = st.relid 
          AND pgtd.objsubid = 0
        LEFT JOIN pg_catalog.pg_description pgcd
          ON pgcd.objoid=st.relid 
          AND pgcd.objsubid = c.ordinal_position
        LEFT JOIN information_schema.tables t
          ON t.table_schema = c.table_schema 
          AND t.table_name = c.table_name
        LEFT JOIN (
          SELECT 
            kcu.table_schema,
            kcu.table_name,
            kcu.column_name
          FROM information_schema.table_constraints tco
          JOIN information_schema.key_column_usage kcu 
            ON kcu.constraint_schema = tco.constraint_schema
            AND kcu.constraint_name = tco.constraint_name
          WHERE tco.constraint_type = 'PRIMARY KEY'
          ORDER BY 
            kcu.table_schema,
            kcu.table_name
        ) AS pks
          ON pks.table_schema = c.table_schema
          AND pks.table_name = c.table_name
          AND pks.column_name = c.column_name
        LEFT JOIN (
          SELECT 
            kcu.table_schema, 
            kcu.table_name,
            kcu.column_name,
            rel_kcu.table_name AS reference_tbl,
            rel_kcu.column_name AS reference_col
          FROM information_schema.table_constraints tco
          JOIN information_schema.key_column_usage kcu
            ON kcu.constraint_schema = tco.constraint_schema
            AND kcu.constraint_name = tco.constraint_name
          JOIN information_schema.referential_constraints rco
            ON rco.constraint_schema = tco.constraint_schema
            AND rco.constraint_name = tco.constraint_name
          JOIN information_schema.key_column_usage rel_kcu
            ON  rel_kcu.constraint_schema = rco.unique_constraint_schema
            AND rel_kcu.constraint_name = rco.unique_constraint_name
            AND rel_kcu.ordinal_position = kcu.ordinal_position
          WHERE tco.constraint_type = 'FOREIGN KEY'
          ORDER BY 
            kcu.table_schema,
            kcu.table_name
        ) AS fks
        ON fks.table_schema = c.table_schema
        AND fks.table_name = c.table_name
        AND fks.column_name = c.column_name
        ORDER by cluster, schema, name, col_sort_order;```

Problem solved, it was on our side, thanks a lot for your help :slightly_smiling_face:

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?

<@U034ZQXKG7M> 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”

Screen Shot 2022-02-28 at 4.10.47 PM.png

It should be there on the right hand side.. It's just very faint :mag:

Ahh, I was looking in the "destination" where the descriptions are empty. Sorry about that!

Hey <@U032M281Z7B>, other users that experienced the viewer bug that you had last week needed to log out and log back in before the integrations showed up again. If that doesn’t work let me know!

Thanks for reporting <@U031RQ46V1R> , it’s likely a bug on our end but let me double check this morning and I'll let you know what we find. 

Hey <@U031RQ46V1R> you were correct that it was a bug. We’ve pushed a fix so if you run an extraction for dbt you should see lineage with the source and child models :slightly_smiling_face:

We're not able to see our dbt source tables in the lineage, any reason why? We can only see lineage between dbt models.

Interesting that is still happening for you. In our dbt integration we were originally just extracting `model` node types from the `manifest.json`. Then in the changes we pushed above, we removed that constraint to pull in the other node types, which lead to the issue you experienced earlier this week with tests being pulled in. Now we’ve limited the extraction to pull in `model` and `seed` types from the manifest.json. Do you know <@U031Z4XUNMD> if in your `manifest.json` you have the source tables as `seed` nodes? If so they should be pulled into Secoda lineage.

Okay that makes sense. We are seeing `seed` nodes but we'd also like to eventually see connections to `source` nodes. These source tables are being pulled in by  our snowflake integration but can't see lineage to downstream `model` nodes.

Ah that makes sense <@U031Z4XUNMD>. <@U03113A05T6> has been working this week on improving lineage through SQL parsing Snowflake queries, so it’ll be good to see if those changes are able to resolve this issue.

Okay great! But also, these `source` nodes are defined in the `manifest.json` file too

Oh okay, I’ll update the integration to also accept `source` nodes. That’s a quick fix

Let me check the issue, thanks for reporting Kevin.

This issue should be resolved now <@U02739P8464>

Andrew, seeing an empty catalog now, only users are showing. Not sure if other accounts are impacted.

Can you check if this is resolved now <@U02739P8464>, we’re fixing this issue permanently this week

Hi <@U035ZUNQN9G> our API currently doesn’t support that, but I can add it to our backlog. It would be good to hear the use case you’re trying to accomplish with that? Are you programatically adding dictionary terms?

Yes ! Im using python for automate the dicts using python

That’s great, glad to hear you’re using the API. I’ll let you know when we’ve added this functionality to the API. Here’s the backlog ticket you can follow <https://linear.app/secoda/issue/SEC-412/api-dictionary-term-set-published>

Hey <@U035TE829K7> let me take a look at our logs to see if I can find out what the issue is.

Just going to move our conversation to a DM.

We’ve pushed a fix for this, you should see the extractions running daily now <@U02739P8464>

Let me take a look into this <@U031Z4XUNMD>. I’ll have a response shortly

This has been fixed now <@U031Z4XUNMD>. Just need to hard refresh to get the newest version of the app

Hey Justin, you’re correct that you’d need both Dataset X and Dataset Y to see column lineage from Dataset X to Dataset Y.

One thing to mention is we’re doing some updates on the column lineage parser this week based on feedback from users last week when we released column lineage which will improve the column lineage from where it is today.

Sounds good - will we be able to click into column lineage from the columns view at some point?

When you say “columns view” it would be good to show a screenshot just so I’m sure of what you’re talking about. The main way we’ll be displaying the column lineage is through the toggle on the “Lineage” tab. If you had suggestions we’re always open to them!

On the Columns tab of the Tables view, it seems natural for me to be able to click on something there next to a column and be shown the lineage for that column somehow.

Hey Ryan, we’re in the process of pushing a change. It looks like there was a breaking change with AWS that broke our encryption. I’ll let you know when the change is live

Hello Ryan, we just pushed an update that should fix this issue. Could you refresh the page and try again please?

Hey Lemou, what data source are you hoping to connect?

You can integrate MySQL server with Secoda for sure. We have a free tier for small teams that might work for your use case. You can sign up at <http://app.secoda.co|app.secoda.co> if you'd like

That’s correct <@U031Z4XUNMD>, there’s an outstanding bug (being fixed this week) where admins can see all resources in viewer mode.

If they update their name to not be undefined in the Profile settings then they’ll show up. They shouldn’t have an undefined name though, so that’s a bug we have to fix. cc <@U02N4NXTTJR>

They don't seem to be able to update their name on their profile and for some reason, it says "not active user"

Oh that’s an interesting issue, I think what happened was that they were pulled in while doing the dashboard extractions from PowerBI so that’s messed things up when they’ve onboarded. Let me see if I can fix the issue.

Can you let me know if you can see Brett now <@U031Z4XUNMD>

Okay, I now see his name under Groups but he's still not under Members

If you refresh your page, can you see his name?