a key component of any data pipeline is extracting and loading data from a source
system into some sort of landing area and there are many well-known products that focus just
on this particular concept but the reality is each of these does come with a cost and they're
typically built on usage which can get really expensive especially if you have a lot of data
but fortunately the open source community has come forward with a great new tool called airbyte
and airbyte as we said is an open source tool it's an open source data integration tool that plugs
right into any elt pipeline that you're trying to build or one that you already have and while there
are some limitations compared to these other products that are the paid products i'd say in
general airbite is really powerful and is going to be sufficient for most of your use cases they do
also have a cloud hosted option that you can pay for if you don't want to deal with that
hassle of setting up that you know open source infrastructure yourself but i don't want you
to have to pay for that if you don't have to so today i'm going to walk you through step by step
how to get that free open source version of airbright up and running on your machine and start
syncing data to a database server this video is going to cover a lot of topics like elt snowflake
docker these are all things that are part of that modern data stack so if you are new to this or if
you're just looking to learn more about that i have put together a free guide i call it the
starter guide for the modern data stack and it'll help you get a better idea kind of how
this all works together and i'll link that down below okay without further ado let's get started
the first thing we'll need to do is install docker and docker allows you to create containers
on either your machine or you could host this in the cloud somewhere it's slightly different than a
virtual machine where you have different operating systems on each one instead it allows you to use
one host operating system and create multiple apps error byte in this case is an app so
that's what we're going to do so let's get started we're going to install docker desktop
right on our local machine in this case so i'm going to go through the steps of installing this
and let's open it up here we can see the docker desktop interface with containers images volumes
we're not going to cover that here but let's just go through this simple get started just to prove
that docker works again this is not a docker tutorial it's more just to
show you that this is set up so we'll copy and paste this command now that it's installed
it pulled down that image it created a container and now we can see that it's there so if we go
ahead and open this on our local browser here it is this is essentially an example of one of
those apps but it's just the getting started app for docker again we're not going to get into the
specifics of all of this stuff all we're trying to prove here is that we have docker installed
it works and now we can do the next thing which is setting up airbite through docker
so now let's go to the air bytes site we're going to do this open source
then here again it says step one is install docker and then we can just run these commands to
spin it up so we'll go ahead and paste this in the first thing it's going to do is
clone that airbite repo so we'll get that on our local machine and change into that directory and
once that's done we'll run a docker compose up and it's going to spin it up for us as simple as that
and here it is so airbite is now installed running in a container
on docker on our machine again this is our local machine but let's now go to this site
and here it is we can now we're able to access the error byte interface just like that as soon as
you have docker it's as easy as just running these commands we'll add some information here our email
and we can also see down here that it says it's on an alpha version that is something
to consider here with airbite if you're going to use it in production understand
kind of where it's at this development cycle so we'll go through and set up our first connection
we're going to do a google sheet connection something very simple here and for any source
you pick there are different uh directions here that you can follow and this something to point
out here with the google sheets is that if you're going to replicate multiple sheets
which i'm sure would be very common you need multiple connections for each so it's for each
google sheet the first thing we need to do is set up an api connection to allow airbite
to get through so in my case i already have a google cloud account so it's a little easier
you may need to set this up on your end if you're using google sheets we'll select this
option up here to add the google sheets api to our api library and we'll go through here
and now let's just enable this okay so we're all set up here we have
this api ready to go but we need to put some credentials in here so i'll select create
credentials and we'll go through this wizard here all right so now we're going to set up the service
account details create and continue now we can add a role for our sake here i'm just going to do
basic editor we want it to be able to read update create whatever we want from google sheets
so we'll we'll select that option and continue now which users do we want to have
access to i'm the only user on this account so i'll put myself in for both of these and
continue so now we have our google sheets api set up ready to go and we can use this within airbyte
to do what we need to do and we can see there's a specific email in here and this is important for
us particularly for google sheets because we're going to need to share this with this account
as we'll see in a minute but the next step here is we need to create a key we need to create
a new key and it needs to be this json value again this is all specific to google
sheets this may be different for you but i think google sheets is a common example
and here we can see here it's asking for that json key so i'm going to go ahead and
create that and it'll save it to my local machine i'm going to hide this and i'm going to open this
up off of the screen so you can't see it but i am going to copy this value and now i'll
paste it in here next we need to determine which spreadsheet we're going to link to so in our case
we're doing this google sheet it's called text or i have two that we're going to use
so first is text stored it has three tabs within it but you'll see here as soon as we go and we try
to link this it's going to give us an error and that's because we have to share this with that
email as i mentioned before so we have to go back and go to our service account and grab this email
copy this and we're going to share our sheet with the email so paste that email address in there
share that should give it the correct permissions that it needs so if we go back and try this again
now that api should be able to get through via the email and we're all set so now that pass
the next thing we need to do is set up our destination which in my case is snowflake
we're going to move this data from google sheets to snowflakes so if we go through the destinations
we can see there's a ton of options but i'm going to pick snowflake and there are some prerequisites
here this is similar to a lot of other tools where it's best practice to create a specific user
database etc but it also mentions that in terms of snowflake specifically you can also
add some configurations based on your loading method if you're doing a storage bucket or you
know different stages again kind of specific to snowflake but in our case we're going to keep
it very simple we also need to have account admin permissions so if you don't have that you'll have
to ask somebody else to help you out here but what we're going to do is copy and paste all of this
here it's nice that they print this all out for us so let's copy i'm going to switch to account admin
again you're going to need this permission to do any of this stuff because we're making a kind of
account and larger database level permission changes here and we'll paste this in and what
we can see here is it's filling this out for us the different users roles but what we do need to
determine is a password so i'm going to use lastpass to generate a nice secure password
and set that value there but we can see here it's setting those different warehouses schemas etc and
as you go through the rest of the query it's using those values to create these new objects for us
and we're going to grant ownership all this stuff so as soon as so as long as you fill out those
top portions you have account admin permissions you can just go and run this it's going to do
all this for you and create something specific to error byte that your database server snowflake in
our case can can use and we can see it created the database who created the schema so now we're ready
for airbike to start loading data into snowflake so let's go back and we'll go through and finish
filling out uh this information the first thing we need is the account or the host name which is
the url we can get rid of the https and then the role warehouse all this stuff we use the
defaults so i'm just going to go through and add these default values in here for us and
what we're going to do is a username and password we set that password for the user we'll copy this
and just have it connect directly this way we're not adding any parameters and the loading method
like we said there are different options for different stages in snowflake but we're just
going to use the default internal stage to get this going all right so let's go ahead and set up
destination and let this do its thing it looks like there's an extra backslash at the end of the
url so i'm going to go up here and delete that from the host and try this again and this time
we're all good so now we can set up the official connection between them to move the data around
and here is another form that fill out we'll give it a name we can determine the frequency
that it's going to replicate um you know every 24 hours every five minutes whatever
you want we'll call this google sheets text store to snowflake call us whatever you want
but this will be unique to this particular sync or connection i'm going to set this to manual
now the destination name space you can do different options we'll see two the first time
we go through this we'll just keep the default mirror source structure and we'll see what that
looks like then when it deploys and the prefix is something that you can add to each table if
you want so if you're keeping this in the default namespace for example everything's going to fall
under the airbase but if you do the prefix let's say tech underscore store with two underscores
every table within this will have that prefix and you can kind of organize your data
coming through that way and we'll see what this looks like but again here are each of the three
tabs within our individual google sheet that we're working with in this example now the last option
here is the normalization and transformation this has to do with the way the data is sent over you
know by default it's kind of sent over almost like an api where it's all json data but instead you
can have it normalized as a tabular data so that it looks like a table when you see it and it makes
more sense and we'll again we'll see what this looks like here so let's go ahead and set this
up and it's going to start our syncing for us and we can click this link and see the progress we can
see the history of this connection we're now in the connections tab and we can see the status the
time individual log so it's very clear now if we go to snowflake we can see it's already setting up
the landing location for the raw data and in this case that is that raw data is that json only data
it's not the normalized tabular data as we'll see later but again we can click around here and see
different things but and just a reminder again all this is happening on our local machine we're using
this whole thing through a docker container on our local machine and we're getting all this to work
so now this is done let's go ahead and skip through this it should be complete and if we go
back and refresh under our schema we can see not only the raw data which is that json data but we
can see our individual tables are there so if we look at our main tables this is what that tabular
format looks like this is what we want it to look like in this case and we can see all of our
data was moved over along with some additional error byte metadata like ids time stamps etc
so let's change this now to show you another example instead of the default namespace let's
do a custom format let's say we want this to go in a completely separate schema called
textstore and in this case we won't need the specific prefixes but let's say in this case we
want every source to be its own schema i think that's a common scenario so we'll go ahead and
update this and it's going to go through and refresh all of this but i want to show you the
difference between mirroring and custom eventually it did come through and complete let's refresh
and now we can see there's a specific schema now called text store with our data there so
you can see the difference instead of having it all under one schema called airbyte schema
instead we can have it in its own schema and maybe not use the prefixes it's up to you what
you prefer to do so let's go through and drop this refresh and now we're left with just that schema
now let's do this again with a different sheet and this time it's payment data just another
sheet what we're going to do is go through the same steps we know we need to share this with
our service account you know once this is once these basics are set up this can get pretty quick
so we've shared this it now has access the api service account has access to our new sheet
so we'll go through and we'll create a new one we'll call this google do google sheets
and this time instead of calling it text store let's call it payment app just to differentiate
between the two and copy that service account json the same one as before and now i'm also going to
copy the link to this spreadsheet so everything should be connected if we did this right so
let's go ahead and add this in here and select setup source and it's going to try to connect
and now that was successful as well so we can move forward let's set up a destination we'll
add it to snowflake which already exists we don't have to change anything here we can just
automatically connect it we'll give this a name and this time again we'll call this payment app
instead of text store just to again differentiate between the two we'll say this is a manual run
and we'll do a custom format remember how we just did that before but this time we'll call this
payment app so we can have this loaded to a separate payment app schema in snowflake
here so we'll have all of it within this database now in this scenario i actually have a few hidden
tabs that we don't need we only want one called transaction so i'm going to unselect these we only
want the transactions sheet to be going through with us okay so that's set this time what we'll
do just again to show you let's do just raw data json and now it's going to go through and sync
and if we go in here it's now loaded very quickly but this is what it looks like with just the json
data let's go back to the schema here the actual schema that we're looking at the underlying sheet
has three columns it has date id and payload instead of being flattened out it just puts it
all as a single json object but let's say we want each of those to have their own columns we don't
want it to be that json we can go back here and update this back to normalized see now there's
a new table called just transactions that has those values flattened for us this payload value
is already a json so that's expected but now we have payment app text store error by schema still
exists because we still have this configured as the default in our snowflake connection in airbite
but let's say we were to get rid of that and you know we can see now this is kind of what
it would look like but if we go back to snowflake i can show you where this is happening it's right
here so we have the default database the default schema all of this we could have changed this
if we wanted so you can determine the naming conventions that work best for you but then you
have error bytes set up to load data possibly in individual schemas or all in one schema however
you want to work for you and all this again is done hosted as an open source tool either locally
or maybe on a cloud service as a as a container and the nice thing about setting it up this way
also is we created a warehouse specifically for air bytes so we can see exactly how much
is being charged for this processing and if you're using the open source tool this is really the main
cost that you're going to have so at this point our connections are all set up and we can
start adding more do whatever we want and use airbite to its full potential this is just the
beginning of things you can do with erabite which is pretty amazing considering it is
open source i hope you found this helpful and let me know what you think about airbite or what your
experience has been as a reminder if you're new to the modern stack take your first step in putting
the pieces together by grabbing a copy of my starter guide for the modern stack link below
i think it'll be really helpful for you and thanks as always for watching and i'll see you next week