Our latest episode of Tag1 Team Talks is an insightful guide through the Extract phase of the ETL (Extract, Transform, Load) process in Drupal migrations. Hosted by Janez Urevc, the episode features experts Mike Ryan and Benji Fisher, who offer a deep understanding of data extraction relevant to migrations from older versions of Drupal (6,7,8) or other CMS platforms.

Beyond just insights, the episode dives in with practical experiences and unique scenarios in data migration. It also delves into the historical context of ETL, providing a comprehensive perspective. This series covers various topics for those interested in learning the details and further enhancing their migration skills, including performance tuning and incremental migrations. We encourage you to listen, subscribe, and stay updated with all the episodes in the series.

Welcome to Tag1 Team Talks brought to you by Tag1 Consulting.

With Drupal 7 and Drupal 9 rapidly approaching end of life, we are

hearing people talk about migrating and upgrading more than ever before.

And anyone who's ever been involved with a large scale migration, migrating

a large site or application, from one technology stack to another,

will tell you that it's complex, time consuming, and it demands expertise.

That's why we are bringing you the series of talks diving deep into

the world of Drupal migrations, and who better to guide us than Tag1's

very own Drupal migration experts.

From the masterminds and maintainers of Drupal's migration tooling, to the

individuals behind the most groundbreaking Drupal migrations, we've got an all

star lineup who'll cover everything you need to know about every aspect

of migrating large scale applications.

This team talk is part, of the three part series about ETL.

Extract Transform, and Load Process, which is used by many enterprise migration

systems, Drupal's Migrate included.

In today's episode, we're going to talk about how to use Drupal Migrate

system to extract data from a Drupal 7 database or any other third party source.

Be sure to stick around to the end because we are also going to announce

next few talks in our series.

Let's dive in.

I'm Janez Urevc, Senior Engineer here at Tag1, and a

longtime contributor to Drupal.

I'm joined today by well known top contributors to Drupal, Benji Fisher,

one of the five current Drupal Migrate core subsystem maintainers,

and Mike Ryan, co creator of Migrate.

Welcome.

Thank you for joining me.

Um, we are glad that you are here.

Present.

Okay.

For the beginning, let's, uh, let's explain what ETL stands for.

Um, like where, where does this concept come from?

And, um, What are other ETL systems besides Drupal Migrate?

Mike, I'm sure that you have plenty of experience in this area.

Yeah.

Um, I think the ETL concept really came from mainframe data

migrations, uh, back in the day.

I have little doubt that many of them were coded in COBOL.

Uh, my first introduction actually was before Drupal.

I worked in, uh, HR, human resources information services for a hospital in

Boston, and we were switching HR systems.

So, um, I was, uh, Primary person responsible for doing that migration.

And that was a classic Extract Transform Load.

We would extract the data from the original system into CSV files, and

I loaded them into Microsoft Access, where I created queries to transform

the exported CSV format into the format for importing into the new system.

And then, you know, we'd export those CSV files and import them or load

them into the destination system.

So that was my first introduction to ETL.

So access wasn't the final destination.

That was just your, what you used for the transform stage.

Yes.

Um, we, we didn't have.

You know, uh, database server to play with or anything like that in between.

We're, we're basically limited to, you know, Microsoft Office tools, because

this is, we weren't really a data center.

We weren't engineers.

Or it wasn't an engineering environment.

I was a software engineer, previously, but, um, that I learned a lot.

And then, uh, around this time at a Drupal meetup in Boston, Moshe Weitzman

was also there and people were talking about trying to import raw HTML into

Drupal and, um, you know, Moshe looked across the table at me and said, you know,

there's There's an opportunity here, which is when we got together to, uh, start

developing a migration system for Drupal.

And of course, since we wanted to, and I'm getting ahead of ourselves a little

bit, but, um, the original concept was importing raw HTML, but of course, you

also want to be able to extract from databases or CSVs and so on, which

led to the idea of a pluggable system.

Uh, in specifically.

Relevant to today's talk, uh, pluggable extract, um, plugins.

Did you, did you have like a client or a website where you needed to use this?

Or was just like, uh, you saw the opportunity and you decided

that you want to build a system?

Yeah, we, we started, um, building a system.

I'm trying to remember what the first, was the Economist the first client?

Yeah.

I think there was, there was one before that we had, uh, this is, feels like

ancient history, like what, 15 years ago.

Um, yeah, but we, we saw the need.

We saw that sometimes, um, the Drupal upgrade process could be difficult.

And sometimes it might be easier to treat it as a migration rather

than, uh, with update hooks as was the traditional method at the time.

Yeah, that, that discussion, I already remember, I've been around for that.

That happened like when we were figuring out how people were going to

be upgrading their Drupal 7 sites to Drupal 8, because it was so different.

Yes, the, the update method of it prevented making that sort of massive

change to Drupal really, because there's no way you could really

completely refactor the database.

Um, schema using update hooks.

Yes.

Um.

So so ETL stands for Extract, which is about pulling the data from the legacy

source, basically, Transform, which is about processing the data along the way

and then Load, um, which is basically storing it into the destination system.

And today's topic is E, extract.

So let's, let's dive into that.

Um, what is being done as part of the extract phase and more importantly,

how is that done in Drupal, Benji?

So there are a variety of plugins.

Um, yeah, typically when, or typically we're doing a migration from Drupal

six or seven and we have a database.

So we have, so we know a lot about the structure of the data and there are.

Uh, what we call source plugins built into Drupal.

Um, Drupal Migrate API has its own terminology, so the extract

phase is called source plugins.

Um, but the source can be lots of other things.

If you're not talking about migrating from Drupal 6 or 7,

you might have a CSV source, just a spreadsheet, or a spreadsheet

in some other format like Excel.

Uh, you could have JSON, it might be JSON API, um, XML or SOAP.

Um, someone suggested that you might want to import emails into Drupal and

then you'd have an IMAP formatted file.

I've never seen that, but it's certainly something that could be done.

And, um...

I do want to point out that migrate system can be used not just for creating

a Drupal site out of some legacy system.

It can also be used for recurring migrations.

And typically when, when you're doing that, the source is going

to be any of those formats served over an API from somewhere else.

And you might be importing articles or events.

Users, um, just in any sort of content that you have, um, in any external system

and you want to get it into Drupal.

Yeah.

Or you could use it with commerce, for example, and import, um, products

on an ongoing basis from some other system where that is the main

source of truth for your products.

Yeah, I've, uh, I've worked on projects that were using QuickBooks and we wanted

to get information going both ways.

We wanted to get, um, I guess, prices out of QuickBooks into Drupal and we wanted

to get orders out of Drupal into Commerce.

And I think we'll talk about that a little later, um, or maybe in, uh,

in one of the, the other episodes.

The funny thing is that we ended up using the Migrate API for both directions.

Which, uh, proves how well architected it is, in my opinion.

Like the fact that you can, that you can come up with use cases.

That are definitely not obvious, um, rules, how powerful it is.

And I think that the main or not the main, but one of the reasons why it's so

powerful, it's that it's, uh, architected as a pluggable system, um, where you have

different plugin types and then you can reuse code by implementing plugins and

reusing them on different migrations.

Um, and this is something I would like to ask you, Mike.

Um, because this is so core of the migrate nature.

Um, did you, did you know from the beginning it should be a pluggable

system or how, how did you decide that you want to architect it this way?

Um, and maybe, maybe you can briefly talk about which plugin types do we have.

Um, and yeah, why, why, it was done this way in the first place.

Uh, well, I, I did allude to that earlier, and that is, um, the original

concept we were talking about importing HTML in, um, raw HTML pages into Drupal.

But of course, once you're using migration for, um, Drupal upgrades

or side grades, which actually that was a common use case we saw where

someone wanted to re architect their site on the same version of Drupal.

And transform their, um, content types.

So anyway, um, at the time this was before Drupal had a general purpose entity system

and pretty much every module that had data had its own tables with their own schema.

So you, we needed to have distinct plugins, distinct sources for

each of those, as well as to handle, um, occasionally you'd

need to import a CSV file into a Drupal table or, um, again, HTML.

And once, once you start seeing things as pluggable, it becomes natural to just make

everything pluggable, all the more so, uh, since Drupal 8, which is, um, Entirely

built based on the plugin concept.

Yeah.

Like the idea of plugins was around even before I remember, like

you had to like C tools provided plugins in Drupal seven world and.

Even six, I think, uh, I think the Views module had plugins,

Right?

Yeah, it could be that even the, the thing that was in C tools came

from Views and was then extracted into the C tools because C tools

were a dependency for Views, right?

Yeah.

Earl extracted, uh, C tools from Views.

As I, if I recall correctly.

Yeah, and then in Drupal 8, we got this, it was basically completely

re, re architected plugin system and then everything moved to that.

And, um, yeah, it's quite pleasant to work with in my opinion.

Right.

Even plugins have plugins, you can have annotations or YAML, or

I think there's a third way of specifying the plugin structure.

You can always get very crazy about, you know, we, like when I was working

on the media initiative, um, we also use plugins a lot and made everything

pluggable, like sometimes when I, when I look back at it, maybe we even made

everything too abstract and I think this.

Drupal community loves to make everything abstract and everything pluggable

and, you know, not opinion and that, that's very powerful, but sometimes it

ends up seeing very complex systems.

And now looking back, like I would, I would do some things in a more simple way.

Um, in the media ecosystem, but, um, yeah, Migrate totally made sense.

Every, all the time to me, like I never had issues understanding how it works.

So great job, Mike.

Um, so we already mentioned that going from Drupal 7 to Drupal

8, uh, Migrate became part of core, which is another proof.

Uh, how powerful and how solid it is, um, which means that, um, there is some

basic functionality that ships with Drupal Core, but then, um, we also have

a huge number of modules or plugins in the contributed space, um, which can

help you run more esoteric migrations.

Uh, Benji, could you tell us what, what ships with Core and

what is that mostly used for?

And what would we have to turn to Contrib for?

Right.

So in terms of source plugins for the Extract phase.

Um, Core provides the the API that everything is built on.

So there will be some, um, sort of base classes.

There's a base class for a SQL source.

Um, there's the content entity, um, content entity and

config entity base classes.

Um, but other than the basic things needed for the API.

The criterion for being in core is that, um, the migrate system

was the way to upgrade a Drupal 7 site to Drupal 8 or later.

And so, um, the core migrations are almost entirely focused on that use case.

So, in terms of source plugins, uh, we have Drupal 6

sources and Drupal 7 sources.

That's basically the only thing that is supported in core.

So, for your migration projects, getting your pre Drupal 8 databases migrated into

modern Drupal, um, that's what's in core.

Anything else is going to be in contrib.

Um, The only exception I can think of is the, the content entity, which is

using the current database as a source.

Um, so Mike was talking earlier about side, what did you call it?

Side migrations?

Side grades.

Um,

side grades.

Right, so you, you can do that in the current site.

Using the content entity source and just loop through all of your badly

architected foo content types and creating bar content types out of them.

Um, so for any other source plugins, we have to turn to contrib.

There is the Migrate Plus module, which is maintained by, um.

By Lucas Hedding, one of the, uh, one of the other maintainers, current

maintainers of the Migrate API.

So that's sort of considered core adjacent, um, things that seem generally

useful but don't meet the criterion for being in core go into there.

So there is, um, a source plugin there for SQL table.

And then, uh, really generic, there is a URL source plugin, so anytime

you're getting something from an API, you're, you're going to be using the

URL plugin, and then the Migrate Plus module introduces its own plugins.

So we, we know what the API is, um, we need, typically some

sort of authentication, so there are authentication plugins.

Um, we need to, um, fetch the data and then parse it.

So there are three systems of plugins tied into the URI.

I'm sorry, the URL plugin from Migrate Plus.

And then there are, uh, a bunch of...

Uh, more specialized contrib modules, um, there's one for CSV files, one for other

spreadsheets like Excel, um, there's a module devoted to, um, Drupal Commerce

and, uh, both earlier versions of Commerce and I think some other systems like

Magento are, um, are handled by that.

There's the WordPress Migrate module, um, which is a specialized XML source.

The idea there is you export your WordPress site to XML, which WordPress

provides that functionality, and then we import the XML into Drupal.

Um, there's...

Um,

Just to note, there are other systems that, um, Implement

the WordPress XML schema.

So not just WordPress, but there are certain other, I can't remember

which off the top of my head that use the same, uh, XML schema.

So you could use the WordPress migrate to import from those.

Cool.

I didn't know that.

So no reason to use WordPress anymore, right?

There there's a Views Migration Module, and that's something that we just

decided Drupal Core was too hard.

There are too many variations contrib modules can make their own views plugins.

And we just didn't think there would.

a reliable way to, to migrate views from Drupal 7 into modern Drupal.

There is a contrib module that attempts to do that.

Use it with care because it is a hard problem.

Don't expect it to be the final answer to migrating your views, but

it can at least give you a start.

Um, and I, I just saw yesterday looking at the list there.

There's one that migrates playlists from YouTube.

Um, so, so a lot, there are a lot of these specialized source

plugins in contrib space.

The YouTube one could be used for, uh, like incremental migrations.

That we mentioned earlier, right?

Like if you have a YouTube channel and you want to pull the videos that

you upload there into your Drupal site automatically, you could use that.

Exactly.

Yeah.

I don't, I suppose there might be some sites that would want to download the

actual videos and they could do that.

Typically we leave the videos on YouTube, let YouTube do what it does well.

And, and migrate the metadata of the playlists into the Drupal site, and

that would be recurring as you make new playlists as you add to the playlists.

You'd want to import the new data.

Yeah, you could have a, um.

Uh, cron job that runs the migration.

It's amazing, like how thriving the contrib is.

And, you know, that's usually the case in Drupal, but you know, it

amazes me over and over again, how many things developers come up with.

And, you know, the ideas that people have just mind blowing, like sometimes

you, you run into things that, that you would never think about and yet somebody

thought about it and have need for it.

Um, it's, it's really, truly amazing.

Um, so I personally have done a few migrations in my career,

but they were mostly, uh, migrations from third party CMSs.

Um, I worked a lot with media companies and a lot of times media companies would

have sometimes even internally developed CMSs and, uh, in one of the projects

that I've been involved with, they realized that that is not sustainable.

So they decided to, first they decided that they want to go with

something open source, and then they decided to go to Drupal.

Um, so we had to migrate from that and we were basically using the equal.

source plugin, loading data from the third party legacy database,

and then importing into Drupal.

But I'm pretty sure that Mike Ryan, you have, uh, more, more interesting

use cases that you've experienced, uh, throughout your career.

So can you talk about that?

Um, really would have to do some archeology on my memory to dig that up.

Um, you know, one interesting use case, I can't remember exactly what

project it was, but where there was associated data that the primary content

was in the database, but there was associated content which needed to be.

Um, pulled from a web API.

So the, um, source plugin, the source plugin has hooks that you can implement

to add to what the raw source plugin does.

the source plugin and produces one row of data equivalent to an SQL row at a time.

And you can implement the process row hook to, to add to it.

Within the source plugin, so you can read the additional data from

an API or CSV or so on and add it to that row before it goes into

the transform part of the pipeline.

Interesting.

How did that affect performance?

Because I assume that if you are calling APIs on every row, that can slow the

migration down quite significantly.

Yeah, it's, I mean, it really depends on the source.

Yeah, sure.

Yeah, if you're pulling from a local file, not so bad, or, but if it's a web

API, then that will slow things down.

As long as we're mentioning the process row step, it's worth mentioning that

in a Drupal to Drupal migration.

Um, there are a lot of fieldable entities in Drupal six or Drupal seven, um, content

types of fields on them, for example.

And we, we structure it the same way.

We have the base table that gives us the, say the node IDs if you're

migrating nodes and it produces, uh, you know, a bunch of rows.

And then we use that, that same process, row stage, to attach

all, all of the fields to that row, one, one row at a time.

Yes.

And although I said that it's equivalent to an SQL row, but it's, um, a logical

row in an SQL row, you're going to have, um, scalar, uh, values.

Um, while the row, the, um, row object that we're passing through we can

have multi value fields in there.

So in the prepare row, the, um, if there are associated fields with multiple

values, those can be extracted in the prepare row and plugged into an array.

As a member of the row, that's going to go through the pipeline.

And then the rest of the pipeline knows how to handle.

Uh, arrays and how to process each member of that array within the value.

Benji, I heard that you migrated a single page into several nodes in the past.

Can you tell us about that?

Yeah, this, this was kind of funny.

It was part of, uh, uh, a migration from Drupal 7 to Drupal 9, I think.

And, uh, and most of it was pretty routine, but they also had.

One page, which looked like it was a views listing of some person

content type and each person had, uh, several publications and they

had a picture and so on and so forth.

Um, but in fact, it, it wasn't from structured data.

It was one Drupal 7 basic page.

Um, with 10 or 20 of these, um, things that looked like, like, like nodes,

um, just pasted into the body field.

And, uh, we decided we were going to restructure it and make it what it

looks like and have a person content type, but with structured data on it.

Uh, but we had to extract it from this page since it was only 10 or 20.

And this is the funny part.

I said, you know, get an intern, have them copy and paste.

You will spend as much of the intern's time as you would spend of my time.

Um, And, and the insurance would be a lot cheaper, but they said, no, no,

no, we want to do it automatically.

I said, okay, this will be fun.

And, uh, as far as the source was concerned, um, I, I just downloaded

that page once and put it into the, uh, the directory for, for the

custom module we had for migration.

Um, I ran HTML Tidy on it to make sure it was well formed XHTML.

And, uh, and then I used the URL plugin that I was talking about from

Migrate Plus and, uh, and one of the XML data parsers from Migrate Plus.

And, and I guess the important thing to mention is that, um, even though

it was not technically structured data, it was just one page of HTML,

it did have consistent structure.

So every section of the page started off with an H3.

Um, and directly beneath that there was, uh, an image, um, so there, there

was enough structure there that I could reliably pick it apart and create

fields on the, uh, the person nodes.

That I was creating.

That's very interesting.

And the interesting part is that you've been able to use contrib and not

have needs for custom code with that.

Did you, did you use XPath, uh, to, to parse that markup or something else?

Yeah, and anytime that you're parsing XML, and I, as I said, I created XHTML

and so anytime you're parsing XML, you want to use XPath, which, um, I'll

have more to say about in the episode on the transform or process plugins.

Which, uh, which will be the next episode in this mini series.

Um, so definitely join us again for the "T" part of ETL, which is Transform.

Um, besides this mini series, we have some great talking coming up.

Um, our goal is to put one episode per week over the next

few months, um, to support the community in the migration process.

Um, we will talk about performance because performance is something

that we care deeply about at Tag1 and it applies to migration as well.

Because when you are handling really large data sets, a full

data migration can easily take like 12 hours or sometimes even days.

Um, so we'll do a handful of talks on this topic, including on how

to profile and tune a migration.

Including long running migrations and, uh, we'll also talk on incremental

migrations, which we touched on in today's episode already.

Um, how you can include or exclude things and run a migration on a

subset of data to make it faster.

Uh, then, um, every project owner wants their migration to be a success

and we will dedicate an episode to discuss the most important factors for

a successful Drupal 7 to 10 migration in order to help you successfully

navigate your migration project.

And other topics that we will cover in the future include porting custom code

from Drupal 7 to Drupal 10, the future of migrate tooling, how to port a team.

And so much more.

We hope that you'll tune in and enjoy our upcoming team talks.

A huge thank you to our Tag1 team, Benji Fisher and Mike Ryan.

Thank you for joining me.

Make sure you check out the other segments in this series.

Uh, there will be links to them in the show notes along with all the

other links that we mentioned today.

If you like this talk, please remember to upvote, subscribe, and share it.

Um, you can check our past talks at tag1.

com slash TTT.

That's three T's for Tag1 Team Talks.

Um, as always, we'd love to hear your feedback and any topic suggestions.

You can write us at ttt@tag1.com.

Again, that's three T's for Tag1 TeamTalks.

A big thank you to both our guests and to everyone who tuned in.

Thank you for joining us.