https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# secoda-support
w
This message was deleted.
f
Do I understand this documentation well? If the view DDL is referencing table A in the create view statement, this table A should be added to Related and Lineage tabs of the View resource?
b
Hey Sinisa! That is correct. You should be seeing "Table A" in the related tab for the view.
f
Hi Prab. I am not seeing this, that is why I am asking. My views in Redshift are (by request) created with clause with no schema binding Is it possible I am not getting automatic Lineage because of that?
I basically did not get automatic Lineage for any of the views in my gold schema
👀 1
b
Thanks for the additional context Sinisa! Going to check with our development team here to confirm whether the way the views are being created is leading to no lineage getting made.
Hey Sinisa, hope you're well! For context, do you have any specific examples of expected lineage? For example, if you could share the link to a view in Secoda and the resource to which you expect it to have lineage to (e.g. table, view)?
f
basically none of the views created automatic lineage
but you can take as the example gold.channels
Copy code
create view gold.channels as 
select  
 t.channel_id,
 t.channel_ref,
 t.channel_name,
 t.used_for_replay,
 rac.replay_ad_channel_name as replay_channel_name,
 rac.replay_ad_channel_group as replay_channel_group,
 rac.rap_channel_id as rap_channel_id
from 
 gold.channels_mediapro t 
 left join gold.replay_ad_channel rac 
 	on t.channel_ref  = rac.replay_ad_channel_code 
with no schema binding;
I expect that there is lineage and dependency to the tables gold.channels_mediapro and gold.replay_ad_channel based on the DDL of this view
👍🏽 1
Did you maybe find something for this? If it can help you we can have a sync
b
Hey Sinisa, the team is still working on this for us. One quick question for you - are there any examples of views that don't have the no schema binding clause and are also not showing lineage or that are showing lineage?
f
Hi. All my views are no schema binding, as they are mostly taking data from spectrum tables. I will now create the test view and sync Secoda, so I can check this example of the "normal" view and lineage
This is regular view on regular tables I created for the test now
Copy code
create view gold.test_view as 
select 
 p.*,
 rap.replay_ad_channel_name
from 
 gold.programs p 
 left join gold.replay_ad_channel rap 
 	on p.channel_ref = rap.replay_ad_channel_code
I synced Secoda and I see no Lineage from gold.test_view to gold.programs and gold.replay_ad_channel. I also don't see any Related tables.
So it does not work also for normal views. It is at least what I can conclude from this test
The user I am using for the sync is super user, so it should have all the privileges needed to extract the info, I guess
b
Appreciate the additional context Sinisa! Sharing this with the team as well.
Hey Sinisa, hope you're well! Just wanted to follow up to confirm that the update that should correct this was deployed. If you could run another sync, please let us know if you're still seeing any issues with lineage for these views.
f
Hi!
😞
no change
b
Hey Sinisa! I've got the development team taking another look into this for us, will keep you posted on what may be causing this.
f
Hi @best-answer-87711 Is there a chance we try again to fix this? Next week I am presenting Secoda to the other Teams inside the company. Would be great if I can show the Lineage feature as it is important and they will ask me about it. If you can not solve it I will be forced to manually add Lineage which is quite the time intensive action for which I am not sure if I can dedicate the time
b
Hi Sinisa, completely understand and will be sharing this with the team. They are working on this for us and should hopefully be able to provide an update soon. As soon as I have confirmation that the fix has been deployed, will share that here.
l
Hi @few-parrot-16520, I am currently looking into the lineage issue and I believe it comes down to permissions/access for the 
secoda_user
that has been created.
Just following up my previous message, could you run the following commands from a superuser or a user with adequate privileges:
Copy code
GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
f
Hi Sonam
I am confused. The user I am using in Redshift integration is a superuser
l
secoda_user
is a superuser?
f
can it be that I am missing some privileges if I am using a superuser
l
Can you try running the two commands for the schemas you have selected?
f
I will now, but I see no point to give the privileges to a superuser
Copy code
GRANT USAGE ON SCHEMA bronze_spectrum TO secoda_user ; --spectrum schema
GRANT SELECT ON ALL tables IN SCHEMA bronze_spectrum TO secoda_user;

GRANT USAGE ON SCHEMA bronze TO secoda_user ;
GRANT SELECT ON ALL tables IN SCHEMA bronze TO secoda_user;

GRANT USAGE ON SCHEMA gold TO secoda_user ;
GRANT SELECT ON ALL tables IN SCHEMA gold TO secoda_user;
No. I don't see lineage generated for the views based on DDL
I was sending the DDL for channels
Here is what I got - no lineage and no related populated
l
Still investigating, we use a number of views from Redshift to pull lineage metadata, it seems that when querying 
information_schema.view_table_usage
, we are not getting back any results
f
I don't know this table for metadata
I use this one
information_schema.view_table_usage definitely does not have all metadata. this is not a good candidate to pull the metadata from
for what I can check information_schema.view_table_usage is not storing info for "*no schema binding*" views
if you check the thread I told you 14 days ago that all my views are with no schema binding
the issue is for sure not related to privileges as I made secoda_user a superuser 14 days ago as well and the issue was not solved
l
We initially were using a combination of 
information_schema.views
and 
information_schema.view_table_usage
but for your workspace when querying 
information_schema.views
it does not return the Query Definition
We switched to 
<http://pg_catalog.pg|pg_catalog.pg>_views
to work around this. That is why I was under the impression of it being some privilege issue. Can you try querying 
information_schema.view_table_usage
does it yield any results at all?
f
it returns the result for the views which are created without the option "no schema binding". These views are not in the schemas I am loading with Secoda, as all my views I am loading to Secoda are with option "no schema binding"
👍 1
so this is not returning the result
Copy code
select * from information_schema.view_table_usage
where view_schema = 'gold' and view_name = 'channels'
but this is returning the result
Copy code
select * from information_schema.views
where table_schema  = 'gold' and table_name = 'channels'
l
can you run this query in a query block in secoda:
Copy code
select * from information_schema.views
where table_schema  = 'gold' and table_name = 'channels'
and see if you are getting a view_definitioin are you currently logged in as 
secoda_user
in your sql query program
f
this is weird
if I am logged in as secoda_user
If I am logged in as myself
this with show view works also for the views where user is not the owner, as said in this stack overflow link
l
We will create a ticket to come up with a workaround to parse the View Definition to determine the lineage.
f
Thank you Sonam. Please share if you know how much does it take for the workaround? Based on this info I need to decide if I am doing this presentation or I wait. Thank you for your support
b
Hey Sinisa, once I have some more context on the ETA to get this updated will follow up here! Either way I'll provide an update by tomorrow on whether we're able to get this completed before next week. To confirm, what day were you thinking of presenting?
f
Hi. I have presentation to onboard new teams to Secoda Thu next wek
👍🏽 1
b
Hey Sinisa! Quick update - the team was able to identify the issue and the update is just under review. As soon as I have confirmation that this has been deployed. If not by end of day, this should be deployed over the weekend.
👍 1
f
Hi Prab. Is the update release? Should I try again?
b
Hi Sinisa, hope you're well! The update was released over the weekend. If you could run a sync for the redshift integration first, that would be great! Will keep an eye out of for the sync completion as well to then confirm whether lineage now shows.
f
Hi Prab
I see now the relationships from the views
Thanks!
I am able now to have this presentation
great
b
Awesome! Thanks for confirming Sinisa. If there's anything else we can help with, please let us know.
Open in Slack
PreviousNext
Powered by