{"id":3554,"date":"2022-04-26T06:51:53","date_gmt":"2022-04-26T14:51:53","guid":{"rendered":"https:\/\/www.gudusoft.com\/?p=3554"},"modified":"2022-05-19T01:36:03","modified_gmt":"2022-05-19T09:36:03","slug":"amazon-redshift-data-lineage-gudu-sqlflow","status":"publish","type":"post","link":"https:\/\/www.gudusoft.com\/fr\/amazon-redshift-data-lineage-gudu-sqlflow\/","title":{"rendered":"Lign\u00e9e de donn\u00e9es Amazon Redshift | Gudu SQLFlow"},"content":{"rendered":"

Lign\u00e9e de donn\u00e9es Amazon Redshift | Gudu SQLFlow<\/strong><\/h2>\n

In your Amazon warehouse environment, use Amazon Redshift Spectrum<\/a><\/strong> to query data directly from files on Amazon S3, save data in Redshift databases, and use Business Intelligence tools such as Tableau, PowerBI, Looker, Qlik, Superset to generate reports from the data. The data originates from your business source system and lands on Amazon S3, then using an ETL tool such as DBT to transfer and store it in Redshift Database for later uses.<\/p>\n

\"Lign\u00e9e

Lign\u00e9e de donn\u00e9es Amazon Redshift<\/p><\/div>\n

In order to have an overview of the data flow in your Amazon warehouse system, you need a outil de lign\u00e9e de donn\u00e9es<\/strong> to help you understand how the data arrived at a particular location, as well as the intermediate steps and transformations which happen as the data moves through the business system.<\/p>\n

One way to get the data linage<\/a><\/strong> automatically from the Amazon warehouse environment is to analyze all SQL queries used during the data loading, transforming, and analysis. The good news is that all those SQL statements are stored in the Redshift user activity log<\/a><\/strong> et Gudu<\/strong> SQLFlow<\/strong> can parse those log files to discover the data lineage automatically.<\/p>\n

Redshift User activity log<\/h3>\n

The user activity log is useful primarily for troubleshooting purposes and here we use it for the data lineage discovery. It tracks information about the types of queries that both the users and the system perform in the database.<\/p>\n

Logs each query before it is run on the database.<\/strong><\/p>\n

\n\n\n\n\n\n\n\n\n\n\n\n
Column name<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
recordtime<\/td>\nTime the event occurred.<\/td>\n<\/tr>\n
db<\/td>\nDatabase name.<\/td>\n<\/tr>\n
user<\/td>\nUser name.<\/td>\n<\/tr>\n
pid<\/td>\nProcess ID associated with the statement.<\/td>\n<\/tr>\n
userid<\/td>\nUser ID.<\/td>\n<\/tr>\n
xid<\/td>\nTransaction ID.<\/td>\n<\/tr>\n
query<\/td>\nA prefix of LOG: followed by the text of the query, including newlines.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>
Redshift user activity log<\/figcaption><\/figure>\n

Please check this article<\/a> to see how to enable logging.<\/p>\n<\/p>\n

Amazon Redshift User Activity Audit Log Sample<\/h5>\n<\/p>\n
'2018-05-21T06:00:09Z UTC [ db=prod_sales user=duc pid=99753 userid=95 xid=6728324 ]' LOG: create table SumoProdbackUp.organization as (select * from SumoProd.simpleuser) \r\n'2018-05-21T06:00:09Z UTC [ db=vendor user=ankit pid=36616 userid=53 xid=2956702 ]' LOG: DELETE FROM SumoProd.employee WHERE id = 38; \r\n'2018-05-21T06:20:09Z UTC [ db=dev user=himanshu pid=64458 userid=35 xid=5143208 ]' LOG: drop user testuser3<\/code><\/pre>\n<\/p>\n
 <\/p>\n<\/p>\n
Automatic data lineage analysis<\/h3>\n<\/p>\n
Gudu SQLFlow<\/a><\/strong> is a tool that automated SQL data lineage analysis across Databases, ETL, Business Intelligence, Cloud, and Hadoop environments by parsing SQL Script and stored procedure. It can also analyze the Redshift user activity log files to discover the data lineage and depict all the data movement graphically.<\/p>\n<\/p>\n
Here is a part of a data lineage picture that was generated after analyzing the Amazon Redshift user activity log files:<\/p>\n<\/p>\n
<\/figure>\n<\/p>\n
\"Lign\u00e9e<\/a>
Lign\u00e9e de donn\u00e9es Amazon Redshift<\/figcaption><\/figure>\n<\/p>\n
 <\/p>\n<\/p>\n
Conclusion<\/strong>\u00a0<\/h3>\n
Merci d'avoir lu notre article et nous esp\u00e9rons qu'il pourra vous aider \u00e0 mieux comprendre Lign\u00e9e de donn\u00e9es Amazon Redshift<\/strong>. Si vous souhaitez en savoir plus sur Lign\u00e9e de donn\u00e9es Amazon Redshift<\/strong>, we would like to advise you to visit our website Gudu SQLFlow<\/strong><\/a> for more information. Gudu SQLFlow, as a data lineage analysis tool, can not only analyze SQL script files, obtain data lineage, and perform visual display, but also allow users to provide data lineage in CSV format and perform visual display. Thanks again! ( Edited by Ryan on Apr 26, 2022)<\/strong><\/p>\n<\/div><\/div><\/div>