Technology Explorations in Data & AI

Jonny Daenen (00:01)
Data ingestion can be challenging. You have to manage a lot of sources, many connectors, and tons of glue code. Today, we'll have look at PyAirbyte and see how it makes your data ingestion efforts easier. Let's have a look.

Jonny Daenen (00:22)
Hi everyone. Welcome to technology explorations at Dataminded. In this series, we give you an initial look into new or interesting technologies. My name is Jonny, knowledge lead here at Dataminded, and today we'll be looking into Airbyte. And for that I have brought with me Tarik. Welcome Tarik.

Tarik (00:38)
Hi Jonny. Thank you for having me.

Jonny Daenen (00:40)
how have you stumbled across AirByte? What was the reason you started looking into this?

Tarik (00:45)
The reason is that at Dataminded, like in many companies, we have a lot of knowledge that is scattered across multiple platforms, Google Drive, Notion, Slack, you have it. And I was wondering for a pet project for Dataminded, if there was a way that we could get to all of these data.

which are in multiple sources and ingest them so that we can ask questions about it. And I naturally found Airbyte to ingest part of the data

Jonny Daenen (01:18)
What does Airbyte do?

Tarik (01:20)
So, Airbyte, first it's open source and it's a data integration platform. And it has many, many pre-built certified connectors to a lot of different sources, which you can ⁓ leverage to ingest data and then copy them into hundreds of other sources like data warehouses, data lakes, databases.

Jonny Daenen (01:42)
Okay, so essentially it's bringing data from A to B where A can be many options and B can also be many options. And it comes with a bunch of connectors that allow you to quickly get started. So their selling point, I guess is faster development of pipelines.

Tarik (01:45)
Yep.

Mm-hmm,

indeed. They also have built-in orchestration. They also have support for change data capture. advertised as a low-code, where you can just point and click to select a source and then select another source and move.

Jonny Daenen (01:59)
Okay.

Tarik (02:11)
your data from A to B using their pre-built connectors. For example, just for information, here is what Airbyte would look like if you installed it, like the full Airbyte. And then you can select a source and then you can select a destination.

and you see you have a lot of different sources that you can connect to. You can get data from S3 and send it to BigQuery or whatever. And then it looks like this. But this is not what I've been looking into. What I've been looking into is PyAirbyte. And PyAirbyte, it's a Python library that brings the capabilities of Airbyte into a Python environment. ⁓

Jonny Daenen (02:50)
Okay.

Tarik (02:51)
So first, it's also open source. And what it does is that it provides you access to all the connectors that Airbytes has built over the years or that has been built by the community, but ⁓ with Python so that you can code it. And what I thought was, yes.

Jonny Daenen (03:06)
Okay, so you get the advantage of the ecosystem and the advantage

of having everything as code.

Tarik (03:12)
Yeah, indeed. ⁓

Jonny Daenen (03:13)
And when you talk about

connectors, how many connectors are there? Is there everything you can imagine that's supported or is it still quite limited?

Tarik (03:21)
Yeah, see. 600 connectors on OSS and 550 on cloud.

Jonny Daenen (03:28)
okay. Yeah, I see. And these technologies that we see here, this is not only data technologies, like is something like Slack also supported then?

Tarik (03:38)
Yes, do believe so. For example, Slack here. ⁓ Although in this video, we are going to look at Google Drive

maybe a quick summary of, pyAirbyte is that you can create your own Python scripts. You can maintain it as code. You can reuse all the connectors that are available in Airbyte. And why I chose this to explore Google Drive is that I wanted something that is...

quickly reusable, not reinventing the wheel.

Today we are going to look at transcripts of meetings and presentations that we have at Dataminded, which we call Facts at Breakfasts, where we meet and we have a presentation on Friday mornings and we also have breakfast. And we also have Learning Over Lunch And all of these are

being transcripted, and saved in our Google Drive folder at Dataminded. And here I a few of these that we are going to ingest with Airbyte and load into a Postgres database.

So here you can see the multiple files that we are going to ingest and load. And this is the result. here I have 14 files with different names. And here you can see the content of the files. What we're going to do is that we're going to drop this.

out of my postgres database.

and then we are going to ingest it again.

So here I have a repo And this is the airbyte client.py. So it's quite simple and straightforward. You see here we're importing Airbyte. Import airbyte as a b. And then you have a function called getSource. And you just define

the URL of the Google Drive folder that you want to access. And also you declare your credentials because you need credentials of course to access that Google Drive folder. And here we are using a service account which you can create in the Google console. And it takes the form as a JSON. That JSON is stored in my secrets folder here for the purpose of this video. And then you just define

whatever is needed from the Airbyte documentation. And then you can just source.check and source.read. And voila, you have your files from the Google Drive. After that, in the code, I'm just doing basic cleaning on the files, trying to enforce column names and so on. And then here we are defining

for the postgres connection, defining the database host, the database name, the port, and so on. And then we are inserting into the table all the files that we ingested from Airbyte.

So here I'm just going to do make Airbyte and I created a make file here that will run my demo for me.

Hopefully this works. Yes, yes, it starts the PostgreSQL instance. It does most of the heavy lifting for us so that we can just see how it looks. So here we are in the knowledge inventory demo. That's what I call this little experiment. It's creating the PostgreSQL. Its connection check succeeded for source Google Drive

Jonny Daenen (06:48)
That starts up also your Postgres instance.

Tarik (07:09)
we are reading data from Google Drive and is currently reading the recording, reading five records over five seconds. ⁓ It's not.

Jonny Daenen (07:16)
and a record is in this

case one Google Drive document.

Tarik (07:19)
Yes,

one file. And I have tested it before. You can also get PowerPoints, slides, et cetera. It's going to load the text that is inside. And here it's done. Completed source Google Drive, postgres cache. And then it got loaded into

Jonny Daenen (07:28)
Okay.

Tarik (07:40)
the table. It's not super fast here. You can see 17 records over 17 seconds, which is not a great performance. But you have also the API rate limits from Google Drive, And then also every file requires a separate API call from PyAirbyte.

This is not something that you would use to have information right away. You would either do this in batch processing every night or so if you need, but you also have built-in change data capture with PyAirbyte. So you can do incremental syncs and it can see if there is a file that changed, only load changes and so on.

Jonny Daenen (08:13)
Okay.

I guess then your source needs to be compatible with that. So Google Drive needs to offer this modified timestamp so that it can check what data to load, I assume.

Tarik (08:29)
Yes,

yeah, indeed. Here we can check on our database. We're going to...

Refresh, and you can see here we created again this Google Drive files table with the transcript that we got from Google Drive, and now it's loaded into Postgres. So for example, here let's see, have celebrate talk about Fabric, and we have transcript of the talk here in the content column.

Yes. Yes. Yes.

Jonny Daenen (08:58)
So you have the full document content in that cell available. Okay, nice.

Tarik (09:03)
So that's basically what you can do with pyAirbyte. And this is what I did for this small project. Next, you can reuse these

Jonny Daenen (09:14)
Yeah, indeed. once you have them here, you can do whatever you want with the content. I do have a bunch of extra questions, I think, seeing this demo.

Tarik (09:18)
Yes.

Sure.

Jonny Daenen (09:23)
Okay, so we see this content here and this is in a relational table structure. Was it a lot of effort for you to make that structure and to extract whatever you get from Airbyte into that tabular structure?

Tarik (09:36)
No, was not a lot of effort.

here I converted all of these to a data frame in Pandas.

Jonny Daenen (09:43)
This is something that comes out of the box with the Airbyte API then.

Tarik (09:48)
Yeah, yeah. And then you have the columns and it's just basically I renamed them to have a...

better formatting of the names, selecting only the column we need from the files content and from the files metadata, and then generating an ID if there was no ID. Here we're just making a hash to be able to pinpoint each each files if needed. Generating a name if there was no name because for some reason sometimes there is no name on the file.

Jonny Daenen (10:09)
Yeah, of the content.

Tarik (10:19)
And then just a bit of cleaning. actually everything is built in so the tabular format is there and we're just cleaning the names.

Jonny Daenen (10:30)
ok, so you could have just dumped the toPandas dataframe and dumped it into the table. Okay.

Tarik (10:35)
Yes. Yeah, this

is just so that it looks better.

Jonny Daenen (10:39)
Yeah. I can imagine you want to do a bit more processing. So you could do that here. What I wonder is this is of course only a few documents that you're ingesting. What if we would have like hundreds or thousands? I'm wondering like, will it cache it? Will it give you like slices of these documents? How do you think that would work?

Tarik (10:44)
Yes, yes.

Mm-hmm.

Well, I tried to do it for about 200 files and they loaded, but it about an hour and a half or so. And you had also, yes, you had slides in there. So it was extracting the text from the slides. You have docx in there. So different format types. So it's not...

Jonny Daenen (11:13)
that's quite long.

Yeah.

Tarik (11:30)
quick and I think here you would have to manage on your own how you slice the ingestion so that you, because if you're trying to ingest, yeah, the 200 and it fails at some point, you lose the information that you were trying to ingest. So here you have to manage it, which would not be the same if you were using Airbyte, but you're coding it yourself, so you need to.

Jonny Daenen (11:49)
Exactly.

Yeah.

⁓ Yeah, we are adjusting a limited amount of documents here. So this is doable and in terms of rerunning it, this doesn't take that long, but I can imagine you want to do some form of batching if you are ingesting a full Google Drive.

Tarik (12:10)
Indeed.

because I think what happens here is that all the cool functions like batch processing, CDC and so on are more into the Airbyte thing. And then they also offer multiple pricing options.

And here they just provide the code where you can ingest, but then you have to split it up yourself.

Jonny Daenen (12:29)
And with flat files, if you have PNGs or PDF files in the drive, how does it extract them?

Tarik (12:36)
it just extracts the metadata of the files. It's not extracting the actual content of the image itself. It's just metadata about the image.

Jonny Daenen (12:45)
And to set up, how much work was it because you have a Google Drive account? How difficult is that to get up and running?

Tarik (12:51)
It's actually not that complicated. It's just I can show you where you need to go.

So in Google Drive, so this is my account on Dataminded. And then you have on the Google Cloud console, you have ⁓ in the IAM admin, you have a service account page here. And here you can create a service account, which I did for this project. And this is the service account that you have. And it allows you if you use the JSON that is

that comes with this service account, then you can use it as part of your connection to be able to connect.

Jonny Daenen (13:25)
So you create a service account and then you give it permissions on the drive. But that was something you could do yourself, give it permissions on your folder.

Tarik (13:25)
Mm-hmm.

Yes.

Yes, but the thing is at Dataminded, we have all the personal drive and then you have the shared drive. I could not give access to the shared drive. I would have to ⁓ ask an admin of that shared drive, but my personal folder, I could give it access to it.

Jonny Daenen (13:39)
Yeah.

Yeah, I see. So if you want to roll this out on an organizational drive, you would need to have administrative permissions or at least approval. That makes sense.

Tarik (13:51)
Yes.

Yeah, indeed. Indeed, yes.

Jonny Daenen (13:58)
When I saw your data, I saw a lot of, or some values that were not filled in, like the names of the documents, I think. Was that correct?

Tarik (14:06)
Mm-hmm.

Yes.

so here we generate the name from content if it's missing. So it's not always there. And you can see it works for some of the files where you have the name of the meeting at the beginning of the transcript and then it works, but then for some it didn't.

Jonny Daenen (14:23)
Okay, but this

is something you control in your code, you extract it from the file. it's up to you to fill in these columns and to manipulate the data in the right way.

Tarik (14:26)
Yes.

Yes, you have a

lot of control over how you ingest and how you store it, which is one of the pros of this approach.

Jonny Daenen (14:43)
Yeah, okay,

if you would run this in a tool like Airflow for orchestration, how would you approach this?

Tarik (14:51)
I think the same way that I've been doing here, which is using Docker and

running it on, on airflow.

Jonny Daenen (15:00)
so you wrap it in a Docker image and then you run the container. And there you will probably face also these limitations in terms of container size or memory size.

Tarik (15:02)
Yes. Yes.

Indeed.

Jonny Daenen (15:12)
anything else that you'd like to mention or show?

Tarik (15:16)
No, apart that we would have ⁓ another video about what we can do with these files now that they're in there.

Jonny Daenen (15:23)
Maybe a small teaser on what we will still do.

Tarik (15:26)
Yeah, because now we have these files in Postgres and of course you want to do something with them, right? the whole idea for me was that how can I use the power of LLMs to ask questions about what is inside. So we will see how we can do that with MindsDB, where we can create agent and create knowledge bases out of these files that we ingested in Postgres.

Jonny Daenen (15:53)
that sounds like a good next video.

what is your personal opinion now on pyAirbyte?

Tarik (15:58)
I think it's quite nice if you want to quickly try to ingest some files how you set up the code to access to Google Drive was a relatively easy and pain-free method. It's just a pip install and then look at the documentation and write some code.

Jonny Daenen (16:16)
Okay, then let's wrap it up for PyAirbyte.

Tarik (16:20)
Very short, it's a simple Python integration. It's very useful for a first proof of concept and it's quite flexible in deployment.

Jonny Daenen (16:27)
My take on this from what I've seen is that we've seen only a limited amount of scope. It's a very simple pipeline. There is still a lot of work to industrialize something like that, but it does allow you to quickly get started to connect a data source to a destination and get quick results without much code.

Tarik (16:35)
Mm-hmm.

Yeah, indeed. So if you're doing a proof of concept, especially if you're trying to get data ⁓ to get transcripts or text base and then ask questions to those corpus using LLMs, then you can quickly get to that point,

Jonny Daenen (17:05)
thanks a lot Tarik for letting us see the world of PyAirbyte and in the next video, let's dive deeper into MindsDB. All right. Thanks Tarik Thanks everybody for watching and we'll see you in the next video on MindsDB. Bye bye.

Tarik (17:14)
Thank you, Jonny.