00:01Hello and, uh, welcome one and all

00:02Today, we will cover data lakehouse

00:03Data lakehouse is a concept that combines elements of both data lake and data warehouse to bring us the best of both worlds

00:13It aims to provide a unified platform for storing, managing, and analyzing both structured and unstructured data

00:19The term is often associated with the evolving landscape of big data and analytics

00:28We have covered data lake in AWS and on premise

00:31I will leave the link to both of the videos in the description below

00:37Data lakehouse improves upon the capabilities of data lake

00:40It brings features such as schema evolution

00:46Transaction consistency, and time travel, and roll back, amongst other features like databases, data lake houses support schema evolution, allowing changes to the data structure without requiring a full rewrite of the data set

01:04This is crucial for handling evolving data sources and changing business requirements

01:09Data lake houses often implement transactional capabilities to ensure data consistency

01:14This means that write operations are atomic, and the system maintains a consistent state, even in the face of failures or partial updates

01:25Time travel enables reproducible queries

01:28That use exactly the same table snapshot, or lets user easily examine changes, version rollbacks allows user quickly to correct problems by resetting the table to a good state

01:40We will build on the data lake concept and introduce new technologies that transform the data lake to a data lakehouse

01:48Previously, we have built a data lake with MinIO, a compatible S3 storage layer

01:54We'll utilize this to stage our data

01:57For the catalog, we have used Hive metastore that allowed us to map S3 files to the tables that enabled us to use quy engine to analyze S3 data

02:09This is great as we can quy files containing structured and Unstructured data, however, this catalog does not have the capabilities to handle schema changes or to ensure the data is in a consistent state

02:24We had to maintain and update the files in S3

02:27In this iteration, we replace the Hive metastore with Project Nessie

02:32It works with Apache Iceberg table format

02:35A table format allows us to manage the data files in our data lake and brings in the data management capabilities, such as the ability to perform DML, time travel, and rollbacks

02:49It forms the basis of a data lakehouse architecture

02:53Nessie holds the reference to the current metadata file changes to the content

02:58Of the data lake are recorded in Nessie as commits without copying the actual data

03:03Nessie catalog brings in the asset guarantees to any transaction to the Iceberg table, therefore, users get a consistent view of all the data processing engine

03:13For this specific demo, we will use Dremio

03:17We'll use it to create Iceberg tables and do various DML operations on the data files

03:23All of our raw data files will be stored in the data lake, which is an S3 bucket hosted on MinIO

03:32We'll use Docker Compose to set up the data lakehouse with the above text stack

03:38Docker Desktop is a prerequisite for this

03:40Let's create the docker

03:43Containers for S3, Nessie, and Dremio, if you're running this for the first time, it'll take some time to download the images from Docker Hub, and once the images are downloaded, Docker will create and start the containers

03:57We'll need to configure our S3 and catalog within our processing engine, Dremio

04:04For S3, we need to log in and create keys

04:08Login credentials are defined in the Docker Compose file, so let's grab those, and we'll log into MinIO on the following URL and generate the access and the secret key

04:20Once the keys are generated, we can either copy them or download them for later use

04:26While we are here, let's create a few

04:32Buckets, one to hold the data files, and another to house the catalog schema

04:37We'll call the bucket for the files "Data Lake," and the catalog bucket, we'll call it "Warehouse." In the Data Lake bucket, let's import a folder with a CSV file in it

04:51We'll import this data into an Iceberg table later on

04:53Let's launch and configure Dremio next

04:56On the first launch, we'll need to create a user and provide details along with a password

05:04Once we've logged in, we'll add an S3 bucket as a source in the Dremio

05:07First is the S3 bucket that stores the data, so under sources, we select the S3, provide a name, and we'll supply our access and the secret

05:21key, and let's uncheck the encrypt connection checkbox

05:25In the advanced option section, we find fug connection properties

05:31Make sure the CTAS format is set to Iceberg

05:33We define the S3 endpoint and set it to MinIO

05:37In addition, we set the S3 access and S3 compatibility to True

05:43Finally, we provide our bucket's name

05:47Let's go ahead and save these changes

05:49Upon success, we'll see our data lake bucket as a source in Dremio

05:55Next, we add the Nessie catalog as a source

05:58We define a name for this and provide the Nessie's URL

06:01The authentication type is none

06:03Under storage, we provide the same details, such as our access and secret

06:09key, along with the connection properties, however our root path will be the warehouse bucket

06:16With these changes, let's save the Nessie source, and if this is successful, you'll see Nessie's catalog as a source in Dremio

06:24We have our data lake bucket and Nessie's catalog as our sources

06:31If you click on the data lake, we see the folder, and once we navigate to the next folder in it, we see the data files

06:39We select the file, and on this screen, we set the format, delimiter, and the escape character

06:46We make sure our data looks correct before closing this window

06:51I see that our column headers are not correct, so I'll extract the Column names, this looks much better

06:55Let's save the changes

06:58This redirects us to the query engine

07:02We can run the query against our files here

07:05I'll execute the query to see the data

07:07This query displays the data in the file

07:10Our file changes color, and if we expand it, it tries to infer schema details from the file, however, it's not accurate

07:18We'll fix it with the Iceberg table

07:22Let's navigate to the Nessie's catalog and create a table

07:28We can switch the context in the SQL pane

07:31Once we're in the Nessie's catalog, I'll paste in a table DDL

07:33We are creating a table called sales, and then as a catalog, we define the data type for Each column, let's go ahead and execute the script to create the table

07:45We can go ahead and select from it

07:48It is empty at the moment, and it'll take a few seconds before it appears under the catalog

07:56Now, we copy the S3 data in this table using the copy command

07:59We specify the file location and the file name

08:02Once we execute this, it returns rows inserted, and it will display the number of records we have inserted

08:11In this case, it is 60,000

08:14Upon seeing the table, we see the data in this table now, and our date column is picked up as date

08:19Since this catalog allows us to execute DML operations, we can perform update and/or delete

08:29Operations on this table, let's go ahead and uh look at few examples

08:32Let's put a WHERE clause on this select, WHERE product subcategory name equal caps

08:37If we run this, this only displays data for caps

08:44Now, we run an update statement against this table

08:46Let's update the product subcategory name to AWC caps

08:49Now, if we run the same select again, we should see the product category name values as AWC caps

08:57Great, this verifies that we can run DML operations on our data using this catalog

09:06Also, we can see the changes we are making as commits on the left of the SQL pane

09:11We can load any of these changes and revert back to check the

09:18Schema evolution, let's delete the index column

09:20This is the column A in our data set

09:24After this change, our select should no longer display this column

09:29We can also update the column names with an alter statement

09:34We can also create views based on this data to target specific rows, for example, we can create a view for the US

09:43We can check the table history, since Nessie's catalog keeps track of all the changes and it has a pointer to the current state of the table

09:51We can check the table history and see the changes in this table at various stages

09:56With the help of the snapshot, we can travel back in time and see how this

10:01Table looked at a particular time, in addition, we can see the files associated with this table

10:07Iceberg brings the database functionality to the data lake

10:12We can execute SQL DML statements against our data lake files

10:18It brings transactional consistency and schema evolution to the data lake world

10:24Feel free to explore further on Apache Iceberg

10:27In the next session, we'll explore branching and merging with Nessie

10:32This is all on data lake for now

10:35I hope you enjoy the session, like, share, and subscribe

10:37Take care, and I'll see you in the next video.