DuckPGQ – 一个用于图工作负载的DuckDB社区扩展

# DuckPGQ 源代码: https://duckpgq.org/ DuckPGQ 是 DuckDB 的一个社区扩展，用于图工作负载，支持 SQL/PGQ 标准。它利用 DuckDB 的强大能力，将基于 SQL 的高性能图查询功能直接引入你的分析工作流。 ## 安装与加载 DuckPGQ CLI Python Node.js R Java ``` INSTALL duckpgq FROM community; LOAD duckpgq; ``` ``` import duckdb conn = duckdb.connect() conn.install_extension("duckpgq", repository="community") conn.load_extension("duckpgq") ``` ``` import { DuckDBInstance } from '@duckdb/node-api'; const instance = await DuckDBInstance.create(); const connection = await instance.connect(); await connection.run("INSTALL duckpgq FROM community"); await connection.run("LOAD duckpgq"); ``` ``` library(duckdb) con <- dbConnect(duckdb()) dbExecute(con, "INSTALL duckpgq FROM community") dbExecute(con, "LOAD duckpgq") ``` ``` Connection conn = DriverManager.getConnection("jdbc:duckdb:"); Statement st = conn.createStatement(); st.execute("INSTALL duckpgq FROM community"); st.execute("LOAD duckpgq"); ``` ## 主要特性 - **SQL/PGQ 标准** 支持 SQL/PGQ 的图模式匹配，遵循 SQL:2023 标准。 - **高性能** 借助 DuckDB 实现高效、进程内的图分析。 - **易于安装** 通过简单的设置过程与 DuckDB 无缝集成，无需依赖。 - **开源** 社区驱动、可扩展且免费使用。 ### 跨领域探索 DuckPGQ 社交网络航空数据金融数据 设置 ``` ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/snb.duckdb'; use snb; install duckpgq from community; load duckpgq; CREATE or replace PROPERTY GRAPH snb VERTEX TABLES ( Person, Forum ) EDGE TABLES ( Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id) DESTINATION KEY (Person2Id) REFERENCES Person (id) LABEL knows, Forum_hasMember_Person SOURCE KEY (ForumId) REFERENCES Forum (id) DESTINATION KEY (PersonId) REFERENCES Person (id) LABEL hasMember ); ``` 最短路径查询查找共同好友最受欢迎的人被关注最多的人的论坛数 ``` -- 查找从一个人到所有其他人的最短路径 FROM GRAPH_TABLE (snb MATCH p = ANY SHORTEST (p1:person WHERE p1.id = 14)-[k:knows]->*(p2:person) COLUMNS (p1.id, p2.id as other_person_id, element_id(p), path_length(p)) ); ``` ``` -- 查找两个用户之间的共同好友 FROM GRAPH_TABLE (snb MATCH (p1:Person WHERE p1.id = 16)-[k:knows]->(p2:Person)<-[k2:knows]-(p3:Person WHERE p3.id = 32) COLUMNS (p2.firstName) ); ``` ``` -- 查找最受欢迎的前3个人 FROM GRAPH_TABLE (snb MATCH (follower:Person)-[follows:knows]->(person:Person) COLUMNS (person.id AS personID, person.firstname, person.lastname, follower.id AS followerID) ) SELECT personID, firstname, lastname, COUNT(followerID) AS numFollowers GROUP BY ALL ORDER BY numFollowers DESC LIMIT 3; ``` ``` -- 被关注最多的人发布的论坛数 WITH mfp AS ( FROM GRAPH_TABLE (snb MATCH (follower:Person)-[follows:knows]->(person:Person) COLUMNS (person.id AS personID, person.firstname, follower.id AS followerID) ) SELECT personID, firstname, COUNT(followerID) AS numFollowers GROUP BY ALL ORDER BY numFollowers DESC LIMIT 1 ) FROM mfp, GRAPH_TABLE (snb MATCH (person:Person)<-[fhm:hasMember]-(f:Forum) COLUMNS (person.id AS personID, f.id as forumId) ) mem SELECT mfp.personID, mfp.firstname, mfp.numFollowers, count(mem.forumId) forumCount WHERE mfp.personID = mem.personID GROUP BY ALL; ``` 设置 ``` ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/airline-data/datasets/airline-data-small.duckdb' as airline; use airline; install duckpgq from community; load duckpgq; CREATE PROPERTY GRAPH flight_graph VERTEX TABLES ( aircrafts_data, airports_data, bookings, flights, tickets, seats ) EDGE TABLES ( route SOURCE KEY (departure_airport) REFERENCES airports_data(airport_code) DESTINATION KEY (arrival_airport) REFERENCES airports_data(airport_code), ticket_flights SOURCE KEY (ticket_no) REFERENCES tickets(ticket_no) DESTINATION KEY (flight_id) REFERENCES flights(flight_id), bookings_tickets SOURCE KEY (book_ref) REFERENCES bookings(book_ref) DESTINATION KEY (ticket_no) REFERENCES tickets(ticket_no), boarding_passes SOURCE KEY (ticket_no) REFERENCES tickets(ticket_no) DESTINATION KEY (seat_no) REFERENCES seats(seat_no) ); ``` 机场间最短航线平均票价最贵的座位 ``` FROM ( SELECT unnest(flights) AS flights FROM GRAPH_TABLE ( flight_graph MATCH o = ANY SHORTEST (a:airports_data WHERE a.airport_code = 'UKX') -[fr:route]->* (a2:airports_data WHERE a2.airport_code = 'CNN') COLUMNS (edges(o) AS flights) ) ) JOIN route f ON f.rowid = flights; ``` ``` FROM GRAPH_TABLE ( flight_graph MATCH (b:bookings)-[bt:bookings_tickets]->(t:tickets)-[bp:boarding_passes]->(s:seats) ) SELECT round(avg(total_amount), 2) avg_amount, seat_no GROUP BY seat_no ORDER BY avg_amount DESC; ``` 设置 ``` ATTACH 'https://github.com/Dtenwolde/duckpgq-docs/raw/refs/heads/main/datasets/finbench.duckdb' AS finbench; use finbench; INSTALL duckpgq FROM community; LOAD duckpgq; CREATE OR REPLACE PROPERTY GRAPH finbench VERTEX TABLES ( Account, Company, Loan, Medium, Person ) EDGE TABLES ( AccountRepayLoan SOURCE KEY (accountId) REFERENCES Account (accountId) DESTINATION KEY (loanId) REFERENCES Loan (loanId) LABEL repay, AccountTransferAccount SOURCE KEY (fromId) REFERENCES Account (accountId) DESTINATION KEY (toId) REFERENCES Account (AccountId) LABEL transfer, AccountWithdrawAccount SOURCE KEY (fromId) REFERENCES Account (accountId) DESTINATION KEY (toId) REFERENCES Account (AccountId) LABEL withdraw, CompanyApplyLoan SOURCE KEY (companyId) REFERENCES Company (companyId) DESTINATION KEY (loanId) REFERENCES Loan (loanId) LABEL companyApply, CompanyGuaranteeCompany SOURCE KEY (fromId) REFERENCES Company (companyId) DESTINATION KEY (toId) REFERENCES Company (companyId) LABEL companyGuarantee, CompanyInvestCompany SOURCE KEY (investorId) REFERENCES Company (companyId) DESTINATION KEY (companyId) REFERENCES Company (companyId) LABEL companyInvest, CompanyOwnAccount SOURCE KEY (companyId) REFERENCES Company (companyId) DESTINATION KEY (accountId) REFERENCES Account (accountId) LABEL companyOwn, LoanDepositAccount SOURCE KEY (loanId) REFERENCES Loan (loanId) DESTINATION KEY (accountId) REFERENCES Account (accountId) LABEL deposit, MediumSignInAccount SOURCE KEY (mediumId) REFERENCES Medium (mediumId) DESTINATION KEY (accountId) REFERENCES Account (accountId) LABEL signIn, PersonApplyLoan SOURCE KEY (personId) REFERENCES Person (personId) DESTINATION KEY (loanId) REFERENCES Loan (loanId) LABEL personApply, PersonGuaranteePerson SOURCE KEY (fromId) REFERENCES Person (personId) DESTINATION KEY (toId) REFERENCES Person (personId) LABEL personGuarantee, PersonInvestCompany SOURCE KEY (investorId) REFERENCES Person (personId) DESTINATION KEY (companyId) REFERENCES Company (companyId) LABEL personInvest, PersonOwnAccount SOURCE KEY (personId) REFERENCES Person (personId) DESTINATION KEY (accountId) REFERENCES Account (accountId) LABEL personOwn ); ``` 通过转账发现被冻结的账户按时间筛选高额转账 ``` FROM GRAPH_TABLE ( finbench MATCH (src:Account where src.accountId = 16607023625929101) <-[e1:transfer]-(mid:Account) -[e2:transfer]->(dst:Account where dst.isBlocked = true) COLUMNS (src.accountId as src_id, dst.accountId as dst_id) ) SELECT src_id, dst_id WHERE src_Id <> dst_id; ``` ``` FROM GRAPH_TABLE ( finbench MATCH (src:Account)-[e:Transfer]->(dst:Account) WHERE '2022-07-13 09:18:33.137' < e.createtime AND e.createtime < '2022-09-03 02:31:47.812' AND e.amount > 4829783 ); ``` Daniel ten Wolde ## Daniël ten Wolde DuckPGQ 首席开发者，CWI 博士生，专注于图分析和数据库系统。 ## 开发中免责声明 DuckPGQ 目前是一个 **研究项目**，仍处于 **开发阶段**。我们鼓励你探索和尝试，但请注意可能存在 **错误**、不完整的特性或意外行为。我们非常感谢任何有助于改进和扩展此插件的反馈或错误报告。欢迎分享你的使用体验！