How to use JOIN when importing data from a Postgres SQL database into Kuzu ?

[alexis-UE]

Hi everyone,

I am looking desperately how to use JOIN from using "LOAD FROM" to import data from a Postgres SQL database into my kuzu DB.
Does anyone know how to do so ?

Thanks 🙏

[andyfengHKU]

Let me add my thoughts here. We imagine two ways to use Postgres extension.

If you aim for absolute performance or your data is stable, we encourage doing a ETL pipeline to copy data from Postgres to Kuzu. This can be done with the following

Copy <kuzu_table> FROM <pg_db>.<pg_table>

or if you only interested in a subset of columns

Copy <kuzu_table> FROM ( LOAD FROM <pg_db>.<pg_table> RETURN <columnX>, ....)

On the other side, if your data size is not big, we do also allow on-the-fly join with Postgres table. Something like

MATCH (a:person)
LOAD FROM <pg_db>.<pg_table> 
WHERE a.name = <pg_column>

Let me know if this helps

[alexis-UE]

Hi @prrao87 and @andyfengHKU, thank you very much for your answers.

Actually my usecase is the following:

I have a complex Postgres database with a lot of tables. A part of the database corresponds to "cities" with objects "stations" connected to other stations via the object called "railroad".

I want to leverage the power of kuzu to find paths and do analytics in this graph of cities subpart of the global database.

I would like to copy cities and the railroad connections between from my Postgres DB into kuzu using the COPY FROM feature.

The connections between cities can only be retrieved with JOIN operations. And in addition, some attributes of the cities are foreign keys to an attribute table.

Here is an simplified example of the schemas I am dealing with :

city table

id	city	country_id
1	Berlin	1
2	Paris	2
3	Munich	1
4	Rome	3

country table

id	name
1	Germany
2	France
3	Italy

station table

id	name	city_id
1	Gare Austerlitz	2
2	Gare Montparnasse	2
3	Berlin Hauptbahnhof	1
4	München Hauptbahnhof	3
5	Rome Termini	4

railroad table

id	name	station_start_id	station_dest_id	travel_duration_h
1	Paris-Berlin-1	1	3	10
2	Paris-Munich-SNCF	2	4	9

What I am to get into Kuzu is the following:

• Nodes of type City with the following attributes: id, name and country (field name from the table country)
• Relations of type ConnectedTo with the following attribute travel_duration_h

The connections are then:

1. Paris - [Paris-Berlin-1 (10)] -> Berlin
2. Paris - [Paris-Munich-SNCF (9)] -> Munich

I wanted to be able to do something like:

COPY City FROM pg.city
JOIN pg.country ON pg.country.id = pg.city.country_id
WHERE pg.country.name in ('Germany', 'France')
RETURN pg.city.name, pg.country.name

COPY ConnectedTo FROM pg.railroad
JOIN pg.station AS station_start ON station_start.id = pg.railroad.station_start_id
JOIN pg.city AS city_start ON city_start.id = station_start.city_id
JOIN pg.station AS station_dest ON station_dest.id = pg.railroad.station_dest_id
JOIN pg.city AS city_dest ON city_dest.id = station_dest.city_id
RETURN city_start.name, city_dest.name, pg.railroad.travel_duration_h

I ended up doing SQL queries and. saving the results in pandas dataframes to import them in kuzu via COPY FROM df.

For my needs, the Kúzu's "import from sql" feature appears to lack a JOIN operator. But maybe my approach is naive and you have a better suggestion on how to deal with this problem. I would love to have your thoughts on this.

[semihsalihoglu-uw]

@alexis-UE : You are bringing up a good case here. You are right that Kuzu-PG extension currently does not allow you to run an arbitrary SQL query SQL_Q on the PG side, so that you can get the records you want into the exact shape that you want, and then scan the output of SQL_Q with LOAD FROM. This looks like a good feature we should consider implementing. Can you open an issue about it?

On the other hand, I don't have a better solution than your workaround of running SQL_Q in PG and moving to a dataframe and then moving to Kuzu.

[alexis-UE]

Hi @semihsalihoglu-uw , Thanks for your reply. Yes I can definitely open an issue about it :)

[alexis-UE]

Opened issue #4970