- [Sohaib] Hello, everyone, my name is Sohaib Katariwala
and I am an analytic specialist solutions architect
with AWS.
I'm joined with my colleague Sam Selvan who is also
an analytic specialist solutions architect with AWS.
Today, we will be giving you a demo
of the news Streaming Ingestion feature of Amazon Redshift.
There are existing solutions to ingest streaming data
into Amazon Redshift such as Amazon Kinesis Firehose
that have been available for some time now.
Customers have been asking us
for a way to increase the throughput
and latency of streaming data ingested into Amazon Redshift.
This was a key driver for us to create
the new native streaming ingestion feature for on Redshift.
With Redshift Streaming Ingestion,
the latency of your streaming data
goes from minutes to seconds.
Redshift Streaming Ingestion is entirely configurable
with SQL which makes it very easy to use.
You can define schema for your streaming data
or choose to ingest semi-structured JSON data
with the Redshift SUPER data type.
Now Sam will show you how it works.
First of all, we need streaming data.
You can use existing Kinesis data streams that you have.
For this demo, we'll create a new data stream.
We will use a publicly available tool
that the Kinesis team has created
called the Amazon Kinesis Data Generator.
It allows you to create a template for your JSON payload
using some functions from the Faker Python package
that generates fake data for you.
Once we have a template,
we click on Send Data to start generating
and sending data to a Kinesis data stream.
Now Sam will jump over to the Redshift query editor
and connect to a Redshift cluster.
The first thing he'll do
is create an external schema from Kinesis.
To create the external schema from Kinesis,
he'll give the external schema
a unique and descriptive name.
Then he will specify
that this external schema is from Kinesis.
And finally, he will specify an IAM role
that Redshift will assume
to access the Kinesis data streams.
This role must have access to read the data stream,
as well as list the streams
that you plan on ingesting into Amazon Redshift.
Here is an example of the IAM role policy
we are using for this demo.
You can see that this role
is allowed to DescribeStreamSummary,
GetChardIterator, GetRecords, and DescribeStream.
This role is also allowed to ListStreams
and ListShards for all streams
just for the purpose of this demo.
Once the external schema is created,
Sam will enable case sensitivity
for identifiers in Redshift.
This is because Kinesis stream names are case sensitive.
Next, we want to specify which data stream
we'd like to ingest into Amazon Redshift.
We do this by creating a materialized view
in our external schema
from the Kinesis data stream that we want to ingest.
Make sure to use the exact stream name within double quotes,
keeping case sensitivity in mind.
Now let's look at what goes in the select statement
in our materialized view definition
when ingesting from Kinesis data streams.
The first four columns are approximatearrivaltimestamp,
partitionkey, shardid, and sequencenumber.
These are metadata columns
that are available by default for all Kinesis data streams.
They are helpful for auditing, troubleshooting,
and monitoring your streaming data.
The payload column contains the actual data
in the payload of the data stream.
In this case, we are choosing to ingest the entire payload
into a SUPER data type column in our materialized view.
Sam will run the statement to create the materialized view.
It is important to note that creating
the materialized view doesn't actually populate the data
from the Kinesis data stream into Amazon Redshift.
Once the materialized view is created,
we need to refresh the materialized view to pull the data
from the Kinesis data stream into Amazon Redshift.
Sam will now run the refresh materialize view command
to pull the data from the Kinesis data stream
and materialize it in the materialized view.
The first time you refresh a materialized view
after creating it from a Kinesis data stream,
Redshift will go back to the trim horizon of your data
and pull all the data from that point in your data stream.
Depending on how long the data has been streaming,
it may take longer for the first refresh
to pull all the data from the trim horizon
compared to subsequent refreshes of your materialized view.
Once the materialized view is refreshed,
Sam will run a query to check the max
and min approximate arrival timestamp of the data.
We can see that the minimum timestamp
goes back to when the Kinesis data generator
started streaming data into this data stream
and the max timestamp is just around a few seconds
prior to the current time on Sam's computer.
Next, Sam will select the payload
from this materialized view
and we can see the JSON objects
generated by the data generator
and published into the data stream
are now in our materialized view as a SUPER data type.
Sam will now navigate to the data generator
where he has created another data stream
that is also called EV_Station_Data,
but it has mixed case letters in the name.
He will now create a materialized view
from this different data stream.
For this new materialized view,
Sam is not storing the entire JSON payload
into a single SUPER data type column.
For this stream, the schema is well defined
and he knows the schema of the data that will be streaming,
so he will use Redshift's built-in JSON functions
to extract specific fields from the JSON
and store them into separate columns
in the materialized view.
Here he is also able to define specific columns
as distribution keys and sort key,
just like he could in any other materialized view
or table in Amazon Redshift.
He will now create this materialized view
and refresh it to pull the data
from the Kinesis data stream into Amazon Redshift.
Since the refresh materialized view command
will only pull the data from the Kinesis data stream once,
you can create a schedule to refresh the materialized view
to pull data from your Kinesis data streams
into Amazon Redshift as frequently as you like.
Sam will now show you you how to schedule
the refresh materialized view query
using the Redshift query editor.
He'll navigate to the Amazon Redshift console
and click on his Redshift cluster.
From there, he'll click the Query Data dropdown button
on the top right of the page
and select Query in query editor.
Here he'll connect to his Redshift cluster
and in the query editor,
he'll paste the refresh materialized view command
for a specific materialized view that he created earlier.
He'll then click on Schedule
and provide all the necessary permissions,
as well as scheduling options.
In this case, he's going to schedule the materialized view
to refresh every two minutes
and click on Save Changes.
Now this materialized view will be refreshed
every two minutes by Amazon Redshift
which means Redshift will pull in all the new data
that was published to the Kinesis data streams
since the last time the materialized view was refreshed.
Now Sam will run a query against the new materialized view
to aggregate the data.
In this query, he is converting the connection time
from the materialized view to a timestamp data type.
This is because timestamp data types
are not currently supported for the materialized view.
He is also adding up the kilowatt hours delivered
as the energy consumed
and counting the distinct users.
He's also filtering the data
to only look at the last five minutes of activity.
He will now run the query
and the result will display how many users
have connected to the charging station
and how much energy they have consumed per second
over the past five minutes.
We can see in the result that each row represents a second
and we can see the total number of users
connected to the charging station,
along with a total energy consumed in kilowatt hours.
Sam will now create a new view
on top of the materialized view
to convert the connection time field
from a string to a timestamp data type.
Here he can apply other business logic
as well in this view if you would like to
and use it for his downstream analytics.
This was a demonstration of how you can use
the new Streaming Ingestion feature of Amazon Redshift
to ingest streaming data from Kinesis data streams
into Amazon Redshift directly.
Thank you all for watching.