1
00:00:01,598 --> 00:00:03,529
Data ingestion can be challenging.

2
00:00:03,529 --> 00:00:08,133
You have to manage a lot of sources, many connectors, and tons of glue code.

3
00:00:08,134 --> 00:00:13,318
Today, we'll have look at PyAirbyte and see how it makes your data ingestion efforts
easier.

4
00:00:13,318 --> 00:00:14,487
Let's have a look.

5
00:00:22,072 --> 00:00:22,973
Hi everyone.

6
00:00:22,973 --> 00:00:25,795
Welcome to technology explorations at Dataminded.

7
00:00:25,795 --> 00:00:30,058
In this series, we give you an initial look into new or interesting technologies.

8
00:00:30,059 --> 00:00:34,582
My name is Jonny, knowledge lead here at Dataminded, and today we'll be looking into
Airbyte.

9
00:00:34,582 --> 00:00:36,744
And for that I have brought with me Tarik.

10
00:00:36,744 --> 00:00:37,965
Welcome Tarik.

11
00:00:38,528 --> 00:00:39,291
Hi Jonny.

12
00:00:39,291 --> 00:00:40,578
Thank you for having me.

13
00:00:40,578 --> 00:00:42,633
how have you stumbled across AirByte?

14
00:00:42,633 --> 00:00:45,298
What was the reason you started looking into this?

15
00:00:45,431 --> 00:00:58,199
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.

16
00:00:58,359 --> 00:01:06,525
And I was wondering for a pet project for Dataminded, if there was a way that we could get
to all of these data.

17
00:01:06,525 --> 00:01:13,430
which are in multiple sources and ingest them so that we can ask questions about it.

18
00:01:13,430 --> 00:01:18,276
And I naturally found Airbyte to ingest part of the data

19
00:01:18,276 --> 00:01:19,809
What does Airbyte do?

20
00:01:20,230 --> 00:01:26,615
So, Airbyte, first it's open source and it's a data integration platform.

21
00:01:26,615 --> 00:01:38,825
And it has many, many pre-built certified connectors to a lot of different sources, which
you can uh leverage to ingest data and then copy them into hundreds of other sources like

22
00:01:38,825 --> 00:01:41,857
data warehouses, data lakes, databases.

23
00:01:42,015 --> 00:01:47,917
Okay, so essentially it's bringing data from A to B where A can be many options and B can
also be many options.

24
00:01:47,917 --> 00:01:52,120
And it comes with a bunch of connectors that allow you to quickly get started.

25
00:01:52,120 --> 00:01:56,602
So their selling point, I guess is faster development of pipelines.

26
00:01:56,666 --> 00:01:57,127
indeed.

27
00:01:57,127 --> 00:01:59,170
They also have built-in orchestration.

28
00:01:59,170 --> 00:02:02,431
They also have support for change data capture.

29
00:02:02,503 --> 00:02:11,830
advertised as a low-code, where you can just point and click to select a source and then
select another source and move.

30
00:02:11,986 --> 00:02:15,190
your data from A to B using their pre-built connectors.

31
00:02:15,190 --> 00:02:24,297
For example, just for information, here is what Airbyte would look like if you installed
it, like the full Airbyte.

32
00:02:24,338 --> 00:02:28,481
And then you can select a source and then you can select a destination.

33
00:02:28,575 --> 00:02:31,886
and you see you have a lot of different sources that you can connect to.

34
00:02:31,886 --> 00:02:35,829
You can get data from S3 and send it to BigQuery or whatever.

35
00:02:35,829 --> 00:02:37,429
And then it looks like this.

36
00:02:37,689 --> 00:02:40,431
But this is not what I've been looking into.

37
00:02:40,431 --> 00:02:42,771
What I've been looking into is PyAirbyte.

38
00:02:42,771 --> 00:02:49,935
And PyAirbyte, it's a Python library that brings the capabilities of Airbyte into a Python
environment.

39
00:02:49,935 --> 00:02:51,919
uh

40
00:02:51,919 --> 00:02:53,481
So first, it's also open source.

41
00:02:53,481 --> 00:03:05,980
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 uh with Python so that

42
00:03:05,980 --> 00:03:06,651
you can code it.

43
00:03:06,651 --> 00:03:10,215
And what I thought was, yes.

44
00:03:10,215 --> 00:03:12,038
of having everything as code.

45
00:03:12,205 --> 00:03:13,210
Yeah, indeed.

46
00:03:13,210 --> 00:03:14,687
uh

47
00:03:14,687 --> 00:03:17,090
connectors, how many connectors are there?

48
00:03:17,090 --> 00:03:21,336
Is there everything you can imagine that's supported or is it still quite limited?

49
00:03:21,405 --> 00:03:22,745
Yeah, see.

50
00:03:22,905 --> 00:03:28,745
600 connectors on OSS and 550 on cloud.

51
00:03:28,827 --> 00:03:29,127
okay.

52
00:03:29,127 --> 00:03:30,479
Yeah, I see.

53
00:03:30,600 --> 00:03:38,515
And these technologies that we see here, this is not only data technologies, like is
something like Slack also supported then?

54
00:03:38,617 --> 00:03:41,018
Yes, do believe so.

55
00:03:41,238 --> 00:03:42,558
For example, Slack here.

56
00:03:42,558 --> 00:03:50,461
um Although in this video, we are going to look at Google Drive

57
00:03:50,461 --> 00:03:56,591
maybe a quick summary of, pyAirbyte is that you can create your own Python scripts.

58
00:03:56,591 --> 00:03:58,533
You can maintain it as code.

59
00:03:58,533 --> 00:04:02,476
You can reuse all the connectors that are available in Airbyte.

60
00:04:02,476 --> 00:04:08,354
And why I chose this to explore Google Drive is that I wanted something that is...

61
00:04:08,354 --> 00:04:11,262
quickly reusable, not reinventing the wheel.

62
00:04:11,262 --> 00:04:25,255
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

63
00:04:25,255 --> 00:04:27,098
Friday mornings and we also have breakfast.

64
00:04:27,098 --> 00:04:31,662
And we also have Learning Over Lunch And all of these are

65
00:04:32,560 --> 00:04:36,527
being transcripted, and saved in our Google Drive folder at Dataminded.

66
00:04:36,527 --> 00:04:45,052
And here I have a few of these that we are going to ingest with Airbyte and load into a
Postgres database.

67
00:04:45,148 --> 00:04:51,388
So here you can see the multiple files that we are going to ingest and load.

68
00:04:52,108 --> 00:04:54,817
And this is the result.

69
00:04:54,817 --> 00:04:58,857
here I have 14 files with different names.

70
00:04:58,857 --> 00:05:01,937
And here you can see the content of the files.

71
00:05:02,237 --> 00:05:05,054
What we're going to do is that we're going to drop this.

72
00:05:07,170 --> 00:05:09,559
out of my postgres database.

73
00:05:12,184 --> 00:05:15,127
and then we are going to ingest it again.

74
00:05:17,031 --> 00:05:22,599
So here I have a repo And this is the airbyte client.py.

75
00:05:22,599 --> 00:05:25,442
So it's quite simple and straightforward.

76
00:05:25,442 --> 00:05:27,984
You see here we're importing Airbyte.

77
00:05:28,105 --> 00:05:29,607
Import airbyte as a b.

78
00:05:29,607 --> 00:05:33,211
And then you have a function called getSource.

79
00:05:33,211 --> 00:05:35,230
And you just define

80
00:05:35,230 --> 00:05:38,103
the URL of the Google Drive folder that you want to access.

81
00:05:38,103 --> 00:05:44,369
And also you declare your credentials because you need credentials of course to access
that Google Drive folder.

82
00:05:44,369 --> 00:05:48,354
And here we are using a service account which you can create in the Google console.

83
00:05:48,354 --> 00:05:50,396
And it takes the form as a JSON.

84
00:05:50,396 --> 00:05:55,001
That JSON is stored in my secrets folder here for the purpose of this video.

85
00:05:55,001 --> 00:05:57,743
And then you just define

86
00:05:57,921 --> 00:06:01,133
whatever is needed from the Airbyte documentation.

87
00:06:01,133 --> 00:06:06,956
And then you can just source.check and source.read.

88
00:06:07,176 --> 00:06:11,979
And voila, you have your files from the Google Drive.

89
00:06:14,040 --> 00:06:21,745
After that, in the code, I'm just doing basic cleaning on the files, trying to enforce
column names and so on.

90
00:06:22,946 --> 00:06:25,667
And then here we are defining

91
00:06:26,299 --> 00:06:32,948
for the postgres connection, defining the database host, the database name, the port, and
so on.

92
00:06:32,948 --> 00:06:39,695
And then we are inserting into the table all the files that we ingested from Airbyte.

93
00:06:39,697 --> 00:06:46,932
So here I'm just going to do make Airbyte and I created a make file here that will run my
demo for me.

94
00:06:48,476 --> 00:06:49,826
Hopefully this works.

95
00:06:50,927 --> 00:06:53,508
Yes, yes, it starts the PostgreSQL instance.

96
00:06:53,508 --> 00:06:59,210
It does most of the heavy lifting for us so that we can just see how it looks.

97
00:06:59,210 --> 00:07:01,800
So here we are in the knowledge inventory demo.

98
00:07:01,800 --> 00:07:04,452
That's what I call this little experiment.

99
00:07:04,773 --> 00:07:06,533
It's creating the PostgreSQL.

100
00:07:06,533 --> 00:07:09,055
Its connection check succeeded for source Google Drive

101
00:07:09,055 --> 00:07:14,990
we are reading data from Google Drive and is currently reading the recording, reading five
records over five seconds.

102
00:07:14,990 --> 00:07:17,072
um It's not.

103
00:07:17,072 --> 00:07:19,728
case one Google Drive document.

104
00:07:19,879 --> 00:07:20,879
one file.

105
00:07:21,299 --> 00:07:22,679
And I have tested it before.

106
00:07:22,679 --> 00:07:29,379
You can also get PowerPoints, slides, et cetera.

107
00:07:29,659 --> 00:07:32,379
It's going to load the text that is inside.

108
00:07:32,859 --> 00:07:34,339
And here it's done.

109
00:07:34,739 --> 00:07:37,639
Completed source Google Drive, postgres cache.

110
00:07:38,659 --> 00:07:40,500
And then it got loaded into

111
00:07:40,500 --> 00:07:41,081
the table.

112
00:07:41,081 --> 00:07:42,932
It's not super fast here.

113
00:07:42,932 --> 00:07:49,769
You can see 17 records over 17 seconds, which is not a great performance.

114
00:07:49,769 --> 00:07:59,295
But you have also the API rate limits from Google Drive, And then also every file requires
a separate API call from PyAirbyte.

115
00:07:59,497 --> 00:08:02,872
This is not something that you would use to have information right away.

116
00:08:02,872 --> 00:08:13,043
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.

117
00:08:13,124 --> 00:08:20,500
So you can do incremental syncs and it can see if there is a file that changed, only load
changes and so on.

118
00:08:21,007 --> 00:08:23,574
I guess then your source needs to be compatible with that.

119
00:08:23,574 --> 00:08:30,313
So Google Drive needs to offer this modified timestamp so that it can check what data to
load, I assume.

120
00:08:30,313 --> 00:08:31,718
yeah, indeed.

121
00:08:31,718 --> 00:08:34,087
Here we can check on our database.

122
00:08:34,087 --> 00:08:35,369
We're going to...

123
00:08:37,238 --> 00:08:48,349
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.

124
00:08:48,349 --> 00:08:58,420
So for example, here let's see, have celebrate talk about Fabric, and we have transcript
of the talk here in the content column.

125
00:08:58,626 --> 00:09:00,878
Yes.

126
00:09:00,878 --> 00:09:01,679
Yes.

127
00:09:01,824 --> 00:09:02,178
Yes.

128
00:09:02,178 --> 00:09:03,111
Okay, nice.

129
00:09:03,111 --> 00:09:06,665
So that's basically what you can do with pyAirbyte.

130
00:09:06,665 --> 00:09:10,809
And this is what I did for this small project.

131
00:09:11,190 --> 00:09:14,570
Next, you can reuse these files

132
00:09:14,570 --> 00:09:16,102
Yeah, indeed.

133
00:09:16,102 --> 00:09:19,325
once you have them here, you can do whatever you want with the content.

134
00:09:19,346 --> 00:09:23,052
I do have a bunch of extra questions, I think, seeing this demo.

135
00:09:23,052 --> 00:09:28,750
Okay, so we see this content here and this is in a relational table structure.

136
00:09:28,750 --> 00:09:36,719
Was it a lot of effort for you to make that structure and to extract whatever you get from
Airbyte into that tabular structure?

137
00:09:36,779 --> 00:09:38,362
No, was not a lot of effort.

138
00:09:38,362 --> 00:09:43,110
here I converted all of these to a data frame in Pandas.

139
00:09:43,740 --> 00:09:47,628
This is something that comes out of the box with the Airbyte API then.

140
00:09:48,241 --> 00:09:49,561
Yeah, yeah.

141
00:09:49,721 --> 00:09:57,278
And then you have the columns and it's just basically I renamed them to have a...

142
00:09:57,278 --> 00:10:07,368
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.

143
00:10:07,368 --> 00:10:14,373
Here we're just making a hash to be able to pinpoint each each files if needed.

144
00:10:14,573 --> 00:10:19,238
Generating a name if there was no name because for some reason sometimes there is no name
on the file.

145
00:10:19,238 --> 00:10:21,221
And then just a bit of cleaning.

146
00:10:21,221 --> 00:10:29,685
actually everything is built in so the tabular format is there and we're just cleaning the
names.

147
00:10:30,062 --> 00:10:36,472
ok, so you could have just dumped the toPandas dataframe and dumped it into the table.

148
00:10:36,613 --> 00:10:37,489
Okay.

149
00:10:37,489 --> 00:10:39,691
is just so that it looks better.

150
00:10:39,941 --> 00:10:40,302
Yeah.

151
00:10:40,302 --> 00:10:42,703
I can imagine you want to do a bit more processing.

152
00:10:42,703 --> 00:10:44,323
So you could do that here.

153
00:10:44,564 --> 00:10:49,786
What I wonder is this is of course only a few documents that you're ingesting.

154
00:10:50,026 --> 00:10:53,488
What if we would have like hundreds or thousands?

155
00:10:53,488 --> 00:10:55,488
I'm wondering like, will it cache it?

156
00:10:55,488 --> 00:10:58,351
Will it give you like slices of these documents?

157
00:10:58,351 --> 00:11:00,092
How do you think that would work?

158
00:11:00,414 --> 00:11:13,534
Well, I tried to do it for about 200 files and they loaded, but it about an hour and a
half or so.

159
00:11:13,694 --> 00:11:18,974
And you had also, yes, you had slides in there.

160
00:11:18,974 --> 00:11:22,134
So it was extracting the text from the slides.

161
00:11:23,274 --> 00:11:26,014
You have docx in there.

162
00:11:26,094 --> 00:11:28,294
So different format types.

163
00:11:28,714 --> 00:11:30,078
So it's not...

164
00:11:30,078 --> 00:11:42,438
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,

165
00:11:43,818 --> 00:11:48,938
you lose the information that you were trying to ingest.

166
00:11:48,938 --> 00:11:57,919
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.

167
00:11:57,919 --> 00:12:01,071
oh Yeah, we are adjusting a limited amount of documents here.

168
00:12:01,071 --> 00:12:10,492
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.

169
00:12:10,492 --> 00:12:10,958
Indeed.

170
00:12:10,958 --> 00:12:19,814
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.

171
00:12:19,814 --> 00:12:23,506
And then they also offer multiple pricing options.

172
00:12:23,506 --> 00:12:29,894
And here they just provide the code where you can ingest, but then you have to split it up
yourself.

173
00:12:29,949 --> 00:12:36,220
And with flat files, if you have PNGs or PDF files in the drive, how does it extract them?

174
00:12:36,982 --> 00:12:39,642
it just extracts the metadata of the files.

175
00:12:39,642 --> 00:12:43,562
It's not extracting the actual content of the image itself.

176
00:12:43,562 --> 00:12:45,243
It's just metadata about the image.

177
00:12:45,243 --> 00:12:49,127
And to set up, how much work was it because you have a Google Drive account?

178
00:12:49,127 --> 00:12:51,649
How difficult is that to get up and running?

179
00:12:51,932 --> 00:12:54,254
It's actually not that complicated.

180
00:12:54,254 --> 00:12:56,598
It's just I can show you where you need to go.

181
00:12:56,598 --> 00:13:00,381
So in Google Drive, so this is my account on Dataminded.

182
00:13:00,381 --> 00:13:08,448
And then you have on the Google Cloud console, you have uh in the IAM admin, you have a
service account page here.

183
00:13:08,729 --> 00:13:13,573
And here you can create a service account, which I did for this project.

184
00:13:13,593 --> 00:13:15,376
And this is the service account that you have.

185
00:13:15,376 --> 00:13:19,238
And it allows you if you use the JSON that is

186
00:13:19,363 --> 00:13:25,407
that comes with this service account, then you can use it as part of your connection to be
able to connect.

187
00:13:25,407 --> 00:13:29,670
So you create a service account and then you give it permissions on the drive.

188
00:13:29,670 --> 00:13:33,692
But that was something you could do yourself, give it permissions on your folder.

189
00:13:33,929 --> 00:13:39,768
Yes, but the thing is at Dataminded, we have all the personal drive and then you have the
shared drive.

190
00:13:40,471 --> 00:13:42,374
I could not give access to the shared drive.

191
00:13:42,374 --> 00:13:47,943
I would have to uh ask an admin of that shared drive, but my personal folder, I could give
it access to it.

192
00:13:48,232 --> 00:13:48,863
Yeah, I see.

193
00:13:48,863 --> 00:13:56,432
So if you want to roll this out on an organizational drive, you would need to have
administrative permissions or at least approval.

194
00:13:57,795 --> 00:13:58,841
That makes sense.

195
00:13:58,841 --> 00:14:06,441
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.

196
00:14:06,441 --> 00:14:07,721
Was that correct?

197
00:14:08,073 --> 00:14:08,455
Yes.

198
00:14:08,455 --> 00:14:11,877
so here we generate the name from content if it's missing.

199
00:14:11,998 --> 00:14:13,239
So it's not always there.

200
00:14:13,239 --> 00:14:23,328
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.

201
00:14:23,978 --> 00:14:28,340
Okay, but this is something you control in your code, you extract it from the file.

202
00:14:28,381 --> 00:14:32,756
it's up to you to fill in these columns and to manipulate the data in the right way.

203
00:14:32,756 --> 00:14:42,936
lot of control over how you ingest and how you store it, which is one of the pros of this
approach.

204
00:14:43,223 --> 00:14:44,853
Yeah, okay, cool.

205
00:14:44,707 --> 00:14:50,589
if you would run this in a tool like Airflow for orchestration, how would you approach
this?

206
00:14:51,592 --> 00:14:58,345
I think the same way that I've been doing here, which is using Docker and

207
00:14:58,345 --> 00:15:00,029
running it on, on airflow.

208
00:15:00,893 --> 00:15:05,237
so you wrap it in a Docker image and then you run the container.

209
00:15:05,277 --> 00:15:10,923
And there you will probably face also these limitations in terms of container size or
memory size.

210
00:15:12,531 --> 00:15:15,453
anything else that you'd like to mention or show?

211
00:15:17,017 --> 00:15:23,720
No, apart that we would have uh another video about what we can do with these files now
that they're in there.

212
00:15:23,720 --> 00:15:26,204
Maybe a small teaser on what we will still do.

213
00:15:26,989 --> 00:15:32,633
Yeah, because now we have these files in Postgres and of course you want to do something
with them, right?

214
00:15:32,633 --> 00:15:43,019
the whole idea for me was that how can I use the power of LLMs to ask questions about what
is inside.

215
00:15:43,019 --> 00:15:52,516
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.

216
00:15:53,034 --> 00:15:55,063
that sounds like a good next video.

217
00:15:55,057 --> 00:15:58,100
what is your personal opinion now on pyAirbyte?

218
00:15:58,101 --> 00:16:12,049
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.

219
00:16:12,049 --> 00:16:16,358
It's just a pip install and then look at the documentation and write some code.

220
00:16:16,351 --> 00:16:20,063
Okay, then let's wrap it up for PyAirbyte.

221
00:16:20,063 --> 00:16:22,626
Very short, it's a simple Python integration.

222
00:16:22,626 --> 00:16:27,903
It's very useful for a first proof of concept and it's quite flexible in deployment.

223
00:16:27,997 --> 00:16:34,101
My take on this from what I've seen is that we've seen only a limited amount of scope.

224
00:16:34,101 --> 00:16:35,962
It's a very simple pipeline.

225
00:16:35,962 --> 00:16:45,689
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

226
00:16:45,689 --> 00:16:47,180
without much code.

227
00:16:47,293 --> 00:16:49,354
Yeah, indeed.

228
00:16:49,354 --> 00:17:04,654
So if you're doing a proof of concept, especially if you're trying to get data em to get
transcripts or text base and then ask questions to those corpus using LLMs, then you can

229
00:17:04,654 --> 00:17:05,845
quickly get to that point,

230
00:17:05,845 --> 00:17:14,108
thanks a lot Tarik for letting us see the world of PyAirbyte and in the next video, let's
dive deeper into MindsDB.

231
00:17:14,148 --> 00:17:15,128
All right.

232
00:17:15,248 --> 00:17:20,210
Thanks Tarik Thanks everybody for watching and we'll see you in the next video on MindsDB.

233
00:17:20,210 --> 00:17:21,230
Bye bye.