DEV Community: Rajat Venkatesh

Database Access Stack in Rust

Rajat Venkatesh — Mon, 23 Oct 2023 12:43:44 +0000

I am learning to access databases (specifically Sqlite and Postgres) in Rust. Database access requires a stack of libraries
that consist of the following layers:

A database access API specification. These specifications help library developers to provide a consistent API to access databases.
Low-level database access libraries built on top of the specification.
Object-Relational-Mapper (ORM) to map tables to classes.
Migration Assistants Manage changes to the database schema

There are other types of libraries such as connection pools. However the layers mentioned above are the most common.

A project that uses a database will use libraries for each layer in the stack. An example stack from
3 popular programming languages are:

Layer	Python	Java	Golang
Specification	Python DB API	JDBC	`database/sql`
DB Access Libraries	sqlite3	sqlite-jdbc	sqlite3
ORM	SqlAlchemy	Hibernate	GORM
Migration	Alembic	Flyway	golang-migrate

Data Access Stack in Rust

Database Access API Specification

Rust does not yet have an API specification. While not a specification, the closest
alternative is SQLx
which has re-implemented database access to Sqlite, Postgres and MySQL with the same interface.

The lack of a specification adds a burden for libraries that have to work with different types of
databases. For example, in Java an ORM is initialized with a JDBC driver. The ORM can use classes
like Connection and Statement from the JDBC driver and assume that the APIs and behaviour are consistent
across JDBC drivers.

This is not the case in Rust. Consider the native drivers for Postgres
and MySQL. Both the drivers have a Statement class.

Struct mysql::Statement has the following functions:

pub fn columns(&self) -> &[Column]
pub fn params(&self) -> &[Column]
pub fn id(&self) -> u32
pub fn connection_id(&self) -> u32
pub fn num_params(&self) -> u16
pub fn num_columns(&self) -> u16

Struct postgres::Statement has the following functions:

pub fn params(&self) -> &[Type]
pub fn columns(&self) -> &[Column]

params returns a different type. mysql::Statement has more member functions.

Therefore a library that needs to connect to both MySQL and Postgres has to implement custom code for each one.

Database Access Libraries

There are access libraries for all popular databases across the spectrum of OLTP and OLAP databases.
The main concern is that some popular access libraries are of unknown quality.

The top open source OLTP databases: Postgres, MySQL and SQLite have native and well-documented drivers.

Object-Relational-Mapper & Migration Assistants

There are two popular ORMs which also provide support for migrations:

There is a comparison of both the
projects by the maintainers of SeaORM.

For starter projects both of these look like good choices. I could not find any information on which
one scales better for web applications that have to scale.

My Starter Stack

I am working on a command line application that has to access either Postgres or SQLite databases.
It runs SQL queries and does not need an ORM. I will be using SQLx as it provides the same interface
to access both databases.

The lack of a specification does not affect this project. However, it will be difficult if the
project has to be extended to databases such as Snowflake and Presto in the future.

Two Methods to Scan for PII in Data Warehouses

Rajat Venkatesh — Mon, 29 Nov 2021 15:06:01 +0000

An important requirement for data privacy and protection is to find and catalog tables and columns that contain PII
or PHI data in a data warehouse. Open source data catalogs like Datahub and
Amundsen enable cataloging of information in data warehouses. Moreover, tables and columns
can be tagged including PII and type of PII tags.

The missing piece is to scan, detect and tag tables and columns with PII.

This post describes two strategies to
scan and detect PII as well as introduce an open source application PIICatcher that can be used to scan
data warehouses.

What is PII data?

PII or Personally Identifiable Information is generally defined as any piece of information that can be used to
identify an individual. Traditionally, information such as SSN, mailing, email or phone numbers are considered PII. As
technology has evolved, the scope of PII has increased to include login IDs, IP addresses, geolocation and biometric data.

There are different types of PII data:

Sensitive: is any data that can be used to directly link to an individual such as name , phone numbers, email and mailing address.
Non-Sensitive: is any data that can be used to indirectly linked to an individual such as location and race.

Specifically, PII as defined by Compliance laws are:

GDPR: PII is any data that can be used to clearly identify an individual. This also includes IP addresses, login ID details, social media posts, digital images, geolocation and more.
CCPA: Personal information is defined as information that identifies, relates to, describes, is reasonably capable of being associated with, or could reasonably be linked, directly or indirectly, with a particular consumer or household.
HIPAA: HIPAA also defines PII as any type of information that relates directly or indirectly to an individual.

Beyond the above definition, domains and businesses may have specific PII data collected by them. A simple example is
PHI (Personal Health Information) collected by the health industry. Similarly, bank account or crypto-currency wallet IDs
can also be used to identify individuals.

The following list can be considered as basic or common PII information that all industries need to manage:

Phone
Email
Credit Card
Address
Person/Name
Location
Date
Gender
Nationality
IP Address
SSN
User Name
Password

Challenges

An example record in the patients table in
Synthetic Patient Records with COVID-19 is:

Column Name	Data
Id	f0f3bc8d-ef38-49ce-a2bd-dfdda982b271
BIRTHDATE	2017-08-24
SSN	999-68-6630
FIRST	Jacinto644
LAST	Kris249
RACE	white
ETHNICITY	nonhispanic
GENDER	M
BIRTHPLACE	Beverly Massachusetts US
ADDRESS	888 Hickle Ferry Suite 38
CITY	Springfield
STATE	Massachusetts
COUNTY	Hampden County
ZIP	01106
LAT	42.151961474963535
LON	-72.59895940376188
HEALTHCARE_EXPENSES	8446.49
HEALTHCARE_COVERAGE	1499.08

Note that most of the columns store PII data. However, it can be confusing to detect if a column stores PII data and
the type of PII data. For example, if the scanner only scans the data in SSN then it may detect it as a phone number.
Similarly, M or F in the GENDER column or white in RACE column, do not provide enough context to detect if it is PII
and the type of PII data.In both these cases, it is easier to scan the column names.

Conversely, the payers table stores the name of health insurance companies in the NAME column. In this case, the
scanner has to check the data to detect that the NAME column does not contain PII data.

Techniques to scan and detect PII data

Based on the previous section, the two main strategies to scan for PII data are:

Scan column and table names
Scan data stored in columns

Scan Data Warehouse Metadata

Data engineers use descriptive names for tables and columns to help users understand the data stored in them. Therefore,
the names of tables and columns provide clues to the type of data stored. For example,

first_name, last_name, full_name or name maybe used to store the name of a person.
ssn or social_security maybe used to store US SSN numbers.
phone or phone_number maybe used to store phone numbers.

All data warehouses provide an information schema to extract schema, table and column information. For example, the
following query can be used to get metadata from Snowflake:

SELECT
    lower(c.column_name) AS col_name,
    c.comment AS col_description,
    lower(c.data_type) AS col_type,
    lower(c.ordinal_position) AS col_sort_order,
    lower(c.table_catalog) AS database,
    lower({cluster_source}) AS cluster,
    lower(c.table_schema) AS schema,
    lower(c.table_name) AS name,
    t.comment AS description,
    decode(lower(t.table_type), 'view', 'true', 'false') AS is_view
FROM
    {database}.{schema}.COLUMNS AS c
LEFT JOIN
    {database}.{schema}.TABLES t
        ON c.TABLE_NAME = t.TABLE_NAME
        AND c.TABLE_SCHEMA = t.TABLE_SCHEMA

Regular expressions can be used to match table or column names. For example, the regular expression below detects
a column that stores social security numbers:

^.*(ssn|social).*$

Scan data stored in columns

The second strategy is to scan the data stored in columns. Within this strategy the two sub-strategies are:

Regular Expressions
NLP libraries such as Stanford NER Detector and Spacy

The major disadvantage of this strategy is that NLP libraries are compute intensive. It can be prohibitively expensive
to run NLP scanners even on moderately sized tables let alone tables of millions or billions of rows. Therefore, a
random sample of rows should be scanned. Choosing a random sample is harder than expected. Luckily, a few databases
provide builtin functions to choose a random sample. For example, the Snowflake query below provides a random sample:

select {column_list} from {schema_name}.{table_name} TABLESAMPLE BERNOULLI (10 ROWS)

Once the rows have been extracted, then can be processed using regular expressions or NLP libraries to detect
PII content.

Breaking Ties

As explained in challenges, both techniques are required to detect PII data. However both techniques
are susceptible to false positives and negatives. More often than not, different techniques suggest conflicting
PII types. Detecting the right type is hard and the subject of a future blog post.

PIICatcher: Scan data warehouses for PII data

PIICatcher implements both the strategies to scan and detect PII data in the data warehouses.

Features

A data warehouse can be scanned using either strategies. PIICatcher is battery-included with a growing set of
regular expressions for scanning column names as well as data. It also include Spacy.

PIICatcher supports incremental scans and will only scan new or not-yet scanned columns. Incremental scans allow easy
scheduling of scans. It also provides powerful options to include or exclude schema and tables to manage compute resources.

There are ingestion functions for both Datahub and Amundsen which will tag columns and tables with PII and the type of
PII tags.

Check out AWS Glue & Lake Formation Privilege Analyzer for an example
of how PIIcatcher is used in production.

Conclusion

Column names and data can be scanned to detect PII in databases. Both strategies are required to reliably detect PII
data. PIICatcher is an open source application that implements both these strategies. It can tag datasets with PII and
the type of PII to enable data admins to take more informed decisions on data privacy and security.

Open Source SQL Parsers

Rajat Venkatesh — Fri, 08 Oct 2021 10:38:18 +0000

Parsing SQL queries provides superpowers for monitoring data health. This post describes how to get started on parsing
SQL for data observability.

Query history of a data warehouse is a rich source of information to glean how data is used in your organization.
Many aspects of data observability can be tracked by analyzing query history. For example, query history analysis can
extract:

Popular tables and columns
Unused tables and columns
Column-level lineage
Freshness

These statistics also help to automate common data engineering tasks like:

Backup and Disaster Recovery
Triage Data Quality issues
Track sensitive data and how they are used.

Challenges and Approaches

SQL language is an ISO/IEC standard and the latest version is SQL2016.
However, every database implements the standard
differently, uses different function names for the same operation, and has extensions to access specific custom features.
Therefore, there isn’t one SQL parser for dialects of all popular databases and data warehouses.

Regular expressions is a popular approach to extract information from SQL statements. However, regular expressions quickly
become too complex to handle common features like WITH, sub-queries, windows clauses, aliases and quotes.
sqlparse is a popular python package that uses regular expressions to parse
SQL.

An alternate approach is to implement the SQL grammar using parser generators like ANTLR. There
are similar open source parser generators in other popular languages.

There are multiple projects that maintain parsers for popular open source databases like MySQL and Postgres. For other
open source databases, the grammar can be extracted from the open-source project. For commercial databases, the only
option is to reverse engineer the complete grammar. There are SQL parser/optimizer platforms like Apache Calcite
that help to reduce the effort to implement the SQL dialect of your choice.

Open Source Parsers

Some popular open source databases and data warehouses are:

MySQL/MariaDB

Pingcap parser is a MySQL parser in Go.
SQL Parser in phpmyadmin is a validating SQL lexer and parser with a focus on MySQL dialect.

Postgres

libpg_query extracts the parser (written in C) from the postgres project and
packages it as a stand-alone library. This library is wrapped in other languages by other projects like:

Python: pglast
Ruby : pg_query
Golang: pg_query_go
JS: psql-parser in Node and pg-query-emscripten in the browser
Rust: pg_query.rs

Multiple Engines

queryparser implements Apache Hive, Presto/Trino and Vertica dialects.
zetasql implements BigQuery, Spanner, and Dataflow dialects.

Generic Parsers

Python: sqlparse
Rust: sqlparser-rs
Python: mo-sql-parseing

Platforms

Parser/Optimizer platforms implement the common SQL language features and allow customization as first-class feature
of the platform. Two popular open source projects are:

Apache Calcite is a popular parser/optimizer that is used in popular databases and query engines like Apache Hive, BlazingSQL and many others.
JSQLParser can parse multiple SQL dialects like MySQL, Postgres and Oracle. The grammar can be modified to support other SQL dialects.

Apache Calcite allows customizations at various points of the parsing process.

Parser rules can be changed to support custom syntax.
Conventions such as quotes vs double quotes, case sensitivity.
Add optimizer rules.

Apache Calcite also provides visitors for traversing the SQL execution plan. Visitor pattern is an algorithm to traverse
a SQL plan.

Practical tips to Getting Started

There are many abandoned open source SQL parsers. The first filter is to use a project that will be supported in the
future. For popular databases such as Postgres and MySQL/MariaDB, there are parsers available in multiple programming
languages.

What if there is no parser for your database?

Most teams do not create a parser from scratch. A popular option is to use the Postgres parser and then add custom
SQL syntax. AWS Redshift, Vertica and DuckDB are examples. Use a Postgres SQL parser to parse query history of these
databases to parse the majority of the queries.

Many queries will fail to parse such as UNLOAD in AWS Redshift. If it is important to also parse the variants, consider
modifying the projects to accept the custom grammar OR use a platform like Apache Calcite.

Conclusion

There is a demand for SQL parsers to build reports on database or data warehouse usage. There are a number of good
open-source projects. However, there is a steep learning curve to use these projects and in many cases a project may not
fit your specific requirements.

Struggling with parsing query history? Get in touch

Data Governance 101

Rajat Venkatesh — Fri, 04 Sep 2020 10:08:09 +0000

This post is a recap of my presentation and is a semi-autobiographical journey in helping data teams setup data governance frameworks.

What is Data Governance?

The first step is to understand what is data governance. Data Governance is an overloaded term and means different things to different people. It has been helpful to define Data Governance based on the outcomes it is supposed to deliver. In my case, Data Governance is any task required for:

Compliance: Data life cycle and usage is in accordance with laws and regulations.
Privacy: Protect data as per regulations and user expectations.
Security: Data & data infrastructure is adequately protected.

Why is Data Governance hard?

Compliance, Privacy, and Security are different approaches to ensure that data collectors and processors do not gain unregulated insights. It is hard to ensure that the right data governance framework is in place to meet this goal. An interesting example of an unexpected insight is the sequence of events leading to leakage of taxi cab tipping history of celebrities.

Paparazzi took photos of celebrities in New York City using taxi cabs. The photos had geo-locations and timestamps along with identifying information about taxi cabs like registration and medallion numbers. Independently, the Taxi Commission released an anonymized dataset of taxi trips with time, medallion numbers, fares, and tips. It was possible to link the metadata from photographs and the taxi usage dataset to get the tips given by celebrities.

Data Governance is hard because:

There is too much data
There is too much complexity in data infrastructure.
There is no context for data usage.

There is too much data

The trend is towards businesses collecting more data from users and sharing more data with each other. For example, the image below lists some of the companies PayPal has data sharing agreements.

As companies share and hoard more data, it is possible that they will link these datasets to garner insights that were unexpected by the user.

There is too much complexity

The Data & AI Landscape lists approximately 1500 open-source and commercial technologies. In a small survey, I found that a simple data infrastructure uses 8-10 components. Data and security teams have to ensure similar capabilities in compliance and security across all the parts of the data infrastructure. This is very hard to accomplish.

There is no context for data usage

Analytics, Data Science and AI objectives compete with compliance, privacy, and security. A blanket “Yes” or “No” access policies do not work. More context is required to enforce access policies appropriately:

Who is using the data?
What purpose?
When?

How to get started on Data Governance?

I have found it helpful when working with teams on Data Governance by answering these basic questions:

Where is my data?
Who has access to data?
How is the data used?

Typically teams care only about sensitive data. Every company and team will have a different definition of what is sensitive. Common ones are PII, PHI, or financial data.

It is also important to ensure the process of obtaining answers is automated. Automation will ensure that the data governance framework is relevant and useful when required.

Where is my sensitive data?

A data catalog, scanner, and data lineage application are required to keep track of sensitive data.

An example of a data catalog and scanner is PIICatcher. PIICatcher can scan databases and detect PII data. It can be extended to detect other types of sensitive data. The image shows the metadata stored by PIICatcher after scanning data in AWS S3 in the AWS Glue Catalog.

Typically it is not practical to scan all datasets. Instead, it is sufficient to scan base datasets and then build a data lineage graph to track sensitive data. A library like data-lineage can build a DAG from query history using a graphing library. The DAG can be used to visualize the graph or process it programmatically.

Who has access to my sensitive data?

Most databases have an information schema that stores the privileges of users and roles. This table can be joined with a data catalog where columns with sensitive data are tagged to get a list of users and roles that have access to sensitive data.

How is sensitive data used?

The first is to log usage across all databases. Most databases store query history in the information schema. Big Data technologies like Presto provide hooks to capture usage. It is not advisable to log usage from production databases. Instead, proxies should be used for human access. Proxies can log usage and send the information to a log aggregator where it can be analyzed.

Conclusion
Data Compliance, Privacy & Security is a journey. Data governance is hard but can be tackled by starting with simple questions and using automation extensively.

FAQs on Data Lineage

Rajat Venkatesh — Wed, 22 Apr 2020 12:35:16 +0000

What is meant by data lineage ?

In Biology, lineage is a sequence of species each of which is considered to have evolved from its predecessor.

Similarly, Data Lineage is a sequence of transformations through intermediary systems to a final data set. Each data set
is considered to have been created from its predecessor through a specific transformation. A transformation maybe a
SQL query or a program in a language such as Python or Scala. Data Lineage can be at any granular level - schema, table
or column.

Why is data lineage important ?

Data Lineage is important because it enables important data governance functions such as:

Business Rules Verification
Change Impact Analysis
Data Quality Verification

What is a data lineage tool ?

A Data Lineage Tool captures metadata of all data transformations, organizes the metadata in a graph and provides access
to the graph through visual interfaces and programmable APIs.

In general data lineage tools use two techniques:

Push: ETL platforms push metadata to a data lineage tool during transformations.
Pull: Data Lineage tools scan logs and query history from databases and data lakes and generate lineage after the event.

Some data lineage tools use both techniques.

Are there open source data catalog tools ?

How do you build data lineage solution for databases ?

Choose one of the open source data catalog projects such as Amundsen, Apache Atlas or Data Lineage.
Follow installation instructions of the project. Some require a Hadoop cluster.
Integrate ETL tools, databases and data engines with the data lineage tool.
Integrate ETL tools, databases and data engines with the data lineage tool.

Open Source Data Lineage App in Python

Rajat Venkatesh — Tue, 31 Mar 2020 09:52:54 +0000

Hello,

I want to show an open source Python project data-lineage to visualize and analyze data lineage. The project was developed in collaboration with data teams on data governance initiatives over the last couple of years.

There are a lot of open source and commercial tools to capture data lineage. However there are two main problems expressed by data engineers:

The projects require a lot of effort to get started and maintain.
Requires constant discipline in capturing and sending all the metadata.

Both these factors result in incomplete projects and lost opportunities in improving performance, ROI and data quality.

data-lineage solves these problems by choosing the following goals:

providing fast access to data lineage
simple setup
analysis of the lineage using a graph library

To achieve these goals, data lineage has the following features:

Generate data lineage from query history. Most databases maintain query history for a few days. Therefore the setup costs of an infrastructure to capture and store metadata is minimal.
Use networkx graph library to create a DAG of the lineage. Networkx graphs provide programmatic access to data lineage providing rich opportunities to analyze data lineage.
Use Plotly to visualize the graph with tool tips and other rich annotations. Plotly provides a number of features to provide rich graphs with tool tips, color coding and weights based on different attributes of the graph.

You can get a data lineage graph with less than 10 lines of Python code in a Jupyter Notebook.

Right now data-lineage supports postgres and support for more databases is planned.

I appreciate any feedback and please give it a try if you need data lineage for your work.

Links:

Data Lineage On Redshift

Rajat Venkatesh — Wed, 11 Mar 2020 08:52:10 +0000

Data Lineage is important for data governance and security. In Data warehouses and data lakes, a team of data engineers maintain a canonical set of base tables. The source of data of these base tables
maybe events from the product, logs or third-party data from SalesForce, Google Analytics or Segment.

Data analysts and scientists use the base tables for their reports and machine learning algorithms. They may create derived tables to help with their work. It is not uncommon for a data warehouse or lake to have hundreds or thousands of tables. In such a scenario it is important to use automation and visual tools to track data lineage.

This post describes automated visualization of data lineage in AWS Redshift from query logs of the data warehouse. The techniques are applicable to other technologies as well.

tokern / data-lineage

Generate and Visualize Data Lineage from query history

Tokern Lineage Engine

Tokern Lineage Engine is fast and easy to use application to collect, visualize and analyze column-level data lineage in databases, data warehouses and data lakes in AWS and GCP.

Tokern Lineage helps you browse column-level data lineage

visually using kedro-viz
analyze lineage graphs programmatically using the powerful networkx graph library

Resources

Demo of Tokern Lineage App

Checkout an example data lineage notebook.
Check out the post on using data lineage for cost control for an example of how data lineage can be used in production.

Quick Start

Install a demo of using Docker and Docker Compose

Download the docker-compose file from Github repository.

# in a new directory run
wget https://raw.githubusercontent.com/tokern/data-lineage/master/install-manifests/docker-compose/catalog-demo.yml
# or run
curl https://raw.githubusercontent.com/tokern/data-lineage/master/install-manifests/docker-compose/tokern-lineage-engine.yml -o docker-compose.yml

Run docker-compose

docker-compose up -d

Check that the containers are running.

docker ps
CONTAINER ID   IMAGE                                    CREATED        STATUS       PORTS                    NAMES
3f4e77845b81   tokern/data-lineage-viz:latest   ...   4 hours ago    Up 4

…

View on GitHub

Workload System of Record

A system of record of all activity in databases is a prerequisite for any type of analysis. For example, AWS Redshift has many system tables and views that record all the activity in the database. Since these tables retain data for a limited time, it is important to persist the data. AWS provides scripts to store the data in tables within Redshift itself. For performance analysis the query log stored in STL_QUERY and STL_QUERYTEXT are the most important.

Tokern reads and processes the records in STL_QUERY & STL_QUERYTEXT at regular intervals. It adds the following information for every query:

Type of query such as SELECT, DML, DDL, COPY, UNLOAD etc
Source tables & columns which are read by the query if applicable.
Source files in S3 for COPY queries.
Target table & columns where the data was loaded if applicable.
Target files in S3 for UNLOAD queries.

Data Lineage

Tokern uses the system of record to build a network graph for every table & pipeline. An example for infallible_galois is visualized below.

In the network graph, data moves from left to right. Every node (or circle) represents a table. There is an edge (left to right) to a node if the data load reads from that table. A table can be loaded with data from many tables. For example, the data load for hopeful_matsumoto reads data from hungry_margulis.
The graph can be analyzed programmatically or used to create interactive charts to help data engineers glean actionable information.

Reduce copies of sensitive data

The security engineers at Company S used a scanning tool to find all tables and columns that stored sensitive data like
PII, financial and business sensitive data. They found that there were many more copies than anticipated. An immediate goal
was to reduce the number of copies to reduce the security vulnerability surface area. To achieve the goal they had to:

Identify the owners of the copy.
Understand their motivation to create copies.
Work with owners to eliminate copies or use masked copies of the data.

The challenges to achieving these goals were:

Determine the owners of the data.
Number of conversations required to understand the need for copies and agree on a workaround.

Due to the sheer number of tables, the task was daunting and required an automation tool.

Table specific Network Graphs

Tokern provided security team with actionable information to discover

Owners of copies of data
A profile consisting of the queries used to create the table and read it.

The profile helped the security engineers prepare for the conversation with the owners. On a number of occasions,
duplicate tables were eliminated. In other cases, the workflow was changed to eliminate temporary tables for
intermediate data.

In the example below for upbeat_ellis, two intermediate tables with sensitive data were eliminated from the workflow.

Large Network graphs

Another example of a network graph for crazy_terseshkova is shown below.
As seen, the data path is much more complicated with close to a hundred tasks required to eventually load data into the table. The graph is interactive and can be panned & zoomed to focus on specific parts. Metadata of tasks such as start time, run time and table names are shown by hovering over the nodes in the network graph to deal with such
complicated data pipelines.

Conclusion

Security Engineers need data lineage and related automation tools to manage database security. All databases provide a workload system of record. Data Lineage tools can use this information to visualize data lineage as well as use rules to automate checks.

If open source data lineage tools are interesting to you, check out the lineage github project.

tokern / data-lineage

Generate and Visualize Data Lineage from query history

Tokern Lineage Engine

Tokern Lineage Engine is fast and easy to use application to collect, visualize and analyze column-level data lineage in databases, data warehouses and data lakes in AWS and GCP.

Tokern Lineage helps you browse column-level data lineage

visually using kedro-viz
analyze lineage graphs programmatically using the powerful networkx graph library

Resources

Demo of Tokern Lineage App

Checkout an example data lineage notebook.
Check out the post on using data lineage for cost control for an example of how data lineage can be used in production.

Quick Start

Install a demo of using Docker and Docker Compose

Download the docker-compose file from Github repository.

# in a new directory run
wget https://raw.githubusercontent.com/tokern/data-lineage/master/install-manifests/docker-compose/catalog-demo.yml
# or run
curl https://raw.githubusercontent.com/tokern/data-lineage/master/install-manifests/docker-compose/tokern-lineage-engine.yml -o docker-compose.yml

Run docker-compose

docker-compose up -d

Check that the containers are running.

docker ps
CONTAINER ID   IMAGE                                    CREATED        STATUS       PORTS                    NAMES
3f4e77845b81   tokern/data-lineage-viz:latest   ...   4 hours ago    Up 4

…

View on GitHub

Secure Data Lake with AWS Lake Formation Tutorial

Rajat Venkatesh — Mon, 27 Jan 2020 11:03:41 +0000

AWS Lake Formation helps to build a secure data lake on data in AWS S3.
This blog will help you get started by describing the steps to setup a basic data lake with S3, Glue, Lake Formation
and Athena in AWS. The tutorial will use
New York City Taxi and Limousine Commission (TLC) Trip Record Data
as the data set.

Image by Jerry Hargrove

At the end of the tutorial, you will have a data lake setup with Lake Formation.

Prerequisites

Setup AWS Lake Formation using instructions in Getting Started Tutorial.
After the tutorial, the following users and roles are setup:
- Administrator is the Administrator IAM User
- NycWorkflowRole is the IAM Role for Workflows
- lakeadmin is the Data Lake Administrator
Upgrade to Lake Formation Permissions model as described the Upgrade Tutorial.
Setup AWS Athena

Import Taxi Data into Lake Formation

Allow NycWorkflowRole to read NYC Data

Goto IAM > Policies > Create Policy
Follow the wizard and create a policy with name S3NycBucketRead with the following definition:

    {
      "Version": "2012-10-17",
      "Statement": [
                {
                        "Sid": "VisualEditor0",
                        "Effect": "Allow",
                        "Action": [
                        "s3:GetObject",
                                "s3:ListBucket"
                        ],
                        "Resource": [
                                "arn:aws:s3:::nyc-tlc",
                                "arn:aws:s3:::nyc-tlc/*"
                        ]
                }
      ]
    }

Attach S3NycBucketRead policy to NycWorkFlowRole

Create a database and grant permissions

Head to AWS Lake Formation > Databases > Create Database
Fill the form with details below and click on Create Database
- Name: taxidata
Click on Databases in left navigation pane
Select taxidata.
Click on Actions > Grant
Fill up the form with following details and then click on Grant
- IAM Users and Roles: NycWorkflowRole
- Database Permissions: Create Table, Alter.

Create a Crawler to register the data in Glue data catalog

A Glue Crawler will read the files in nyc-tlc bucket and create tables in a database automatically.

Head to AWS Glue > Crawlers > Add Crawler
Fill in the following details in the wizard and click Finish at the end.

Field	Value
Crawler Name	TaxiCrawler
Tags	project: lake_formation_example
Crawler Source Type	Data Stores
Choose a data Store	S3
Crawl data in	Specified Path in another account
Include Path	s3://nyc-tlc/trip data/
Exclude Patterns	fhv_tripdata_2015* fhv_tripdata_2016* fhv_tripdata_2017* fhv_tripdata_2018* yellow* green*
Add Another Data Store	YES
Choose a data Store	S3
Crawl data in	Specified Path in another account
Include Path	s3://nyc-tlc/misc/
Exclude Patterns	foil shared* uber* .html .zip FOIL_*
Add another Data Store	No
IAM Role	Choose an Existing Role
IAM Role	NycWorkflowRole
Frequency	Run On Demand
Choose an existing database	TaxiData
Prefix added to tables	csv_

In the the crawlers list, select the TaxiCrawler and click the Run Crawler button. This should take less than a minute and it will crawl the CSV files you've not excluded in the taxi S3 bucket.
Database -> Tables and review the tables.

Verify Data in AWS Athena

Fire up athenacli as lakeadmin and run a couple of queries to verify the data looks good.

us-east-2:default> select * from taxidata.csv_trip_data limit 10;

+----------------------+---------------------+------------------+--------------+--------------+---------+-------------------+

| dispatching_base_num | pickup_datetime     | dropoff_datetime | pulocationid | dolocationid | sr_flag | hvfhs_license_num |

+----------------------+---------------------+------------------+--------------+--------------+---------+-------------------+

| B00013               | 2015-01-01 00:30:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 01:22:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 01:23:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 01:44:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 02:00:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 02:00:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 02:00:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 02:50:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 04:45:00 |                  | <null>       | <null>       | <null>  | <null>            |

| B00013               | 2015-01-01 06:30:00 |                  | <null>       | <null>       | <null>  | <null>            |

+----------------------+---------------------+------------------+--------------+--------------+---------+-------------------+

10 rows in set

Time: 4.926s

us-east-2:default> select * from taxidata.csv_misc limit 10;

+------------+-----------------+---------------------------+---------------+

| locationid | borough         | zone                      | service_zone  |

+------------+-----------------+---------------------------+---------------+

| 1          | "EWR"           | "Newark Airport"          | "EWR"         |

| 2          | "Queens"        | "Jamaica Bay"             | "Boro Zone"   |

| 3          | "Bronx"         | "Allerton/Pelham Gardens" | "Boro Zone"   |

| 4          | "Manhattan"     | "Alphabet City"           | "Yellow Zone" |

| 5          | "Staten Island" | "Arden Heights"           | "Boro Zone"   |

| 6          | "Staten Island" | "Arrochar/Fort Wadsworth" | "Boro Zone"   |

| 7          | "Queens"        | "Astoria"                 | "Boro Zone"   |

| 8          | "Queens"        | "Astoria Park"            | "Boro Zone"   |

| 9          | "Queens"        | "Auburndale"              | "Boro Zone"   |

| 10         | "Queens"        | "Baisley Park"            | "Boro Zone"   |

+------------+-----------------+---------------------------+---------------+

10 rows in set

Time: 3.116s

Summary

This tutorial showed how to setup a basic data lake containing NYC Taxi Trip data using AWS Lake Formation.
Now the data lake is ready to provide secure access to data engineers, analysts and data scientists.
We would love to hear if this tutorial was helpful to you. Get in touch using the chat widget.