{"id":482557,"date":"2026-06-05T15:07:03","date_gmt":"2026-06-05T15:07:03","guid":{"rendered":"https:\/\/savepearlharbor.com\/?p=482557"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=482557","title":{"rendered":"\u041f\u043e\u0447\u0435\u043c\u0443 \u043c\u044b \u0432\u044b\u0431\u0440\u0430\u043b\u0438 \u0440\u0435\u043a\u0443\u0440\u0441\u0438\u0432\u043d\u044b\u0435 SQL-\u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0432\u043c\u0435\u0441\u0442\u043e GraphQL \u0434\u043b\u044f \u0433\u0440\u0430\u0444\u0430 \u0437\u043d\u0430\u043d\u0438\u0439"},"content":{"rendered":"
\n

\u041a\u043e\u043d\u0442\u0435\u043a\u0441\u0442<\/h3>\n

\u0423 \u043d\u0430\u0441 \u0435\u0441\u0442\u044c wiki \u043f\u043e \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u0430\u043c \u043d\u0435\u0439\u0440\u043e\u043d\u043d\u043e\u0439 \u043f\u0430\u043c\u044f\u0442\u0438 \u2014 72 \u0441\u0442\u0440\u0430\u043d\u0438\u0446\u044b, \u0445\u0440\u0430\u043d\u044f\u0449\u0438\u0435\u0441\u044f \u0432 AlloyDB Omni (PostgreSQL 16.11) \u0441 pgvector \u0434\u043b\u044f \u0432\u0435\u043a\u0442\u043e\u0440\u043d\u043e\u0433\u043e \u043f\u043e\u0438\u0441\u043a\u0430. \u041c\u044b \u0434\u043e\u0431\u0430\u0432\u0438\u043b\u0438 \u0433\u0440\u0430\u0444 \u0437\u043d\u0430\u043d\u0438\u0439: \u0442\u0438\u043f\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0435 \u0440\u0451\u0431\u0440\u0430 \u043c\u0435\u0436\u0434\u0443 \u0441\u0442\u0440\u0430\u043d\u0438\u0446\u0430\u043c\u0438, 11 \u0442\u0438\u043f\u043e\u0432 \u0441\u0432\u044f\u0437\u0435\u0439, \u0432\u0435\u0441\u043e\u0432\u044b\u0435 \u043a\u043e\u044d\u0444\u0444\u0438\u0446\u0438\u0435\u043d\u0442\u044b. \u0413\u0440\u0430\u0444 \u043e\u0431\u043e\u0433\u0430\u0442\u0438\u043b retrieval: recall \u0432\u044b\u0440\u043e\u0441 \u0441 46.7% \u0434\u043e 68.3%.<\/p>\n

Google \u043d\u0435\u0434\u0430\u0432\u043d\u043e \u043e\u043f\u0443\u0431\u043b\u0438\u043a\u043e\u0432\u0430\u043b context-graph-on-spanner<\/a> \u2014 \u0440\u0435\u0444\u0435\u0440\u0435\u043d\u0441\u043d\u0443\u044e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u044e \u043a\u043e\u043d\u0442\u0435\u043a\u0441\u0442\u043d\u043e\u0433\u043e \u0433\u0440\u0430\u0444\u0430 \u043d\u0430 Cloud Spanner \u0441 \u0432\u0441\u0442\u0440\u043e\u0435\u043d\u043d\u044b\u043c Property Graph \u0438 GQL (Graph Query Language). \u0412\u043e\u0437\u043d\u0438\u043a \u0432\u043e\u043f\u0440\u043e\u0441: \u0441\u0442\u043e\u0438\u0442 \u043b\u0438 \u043f\u0435\u0440\u0435\u043d\u0435\u0441\u0442\u0438 \u043d\u0430\u0448 \u0433\u0440\u0430\u0444 \u043d\u0430 Spanner \u0441 \u0435\u0433\u043e \u043d\u0430\u0442\u0438\u0432\u043d\u043e\u0439 \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u043e\u0439 \u0433\u0440\u0430\u0444\u043e\u0432? \u0418\u043b\u0438 \u043e\u0441\u0442\u0430\u0442\u044c\u0441\u044f \u043d\u0430 AlloyDB?<\/p>\n

\u041c\u044b \u043e\u0441\u0442\u0430\u043b\u0438\u0441\u044c \u043d\u0430 AlloyDB. \u0412\u043e\u0442 \u043f\u043e\u0447\u0435\u043c\u0443.<\/p>\n\n

\u0414\u0432\u0430 \u043f\u043e\u0434\u0445\u043e\u0434\u0430 \u043a \u0433\u0440\u0430\u0444\u0443<\/h3>\n

Spanner: Property Graph + GQL<\/h4>\n

Spanner \u0441 \u0432\u0435\u0440\u0441\u0438\u0438 7.0+ \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u0442 Property Graph \u2014 \u0434\u0435\u043a\u043b\u0430\u0440\u0430\u0442\u0438\u0432\u043d\u0443\u044e \u0441\u0445\u0435\u043c\u0443 \u0433\u0440\u0430\u0444\u0430 \u043f\u043e\u0432\u0435\u0440\u0445 \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446:<\/p>\n

CREATE PROPERTY GRAPH MarketingContextGraph  NODE TABLES (Customers, Decisions, Policies, Outcomes)  EDGE TABLES (    AboutCustomer      SOURCE KEY (decision_id) REFERENCES Decisions (decision_id)      DESTINATION KEY (customer_id) REFERENCES Customers (customer_id),    FollowedPolicy      SOURCE KEY (decision_id) REFERENCES Decisions (decision_id)      DESTINATION KEY (policy_id) REFERENCES Policies (policy_id),    ResultedIn      SOURCE KEY (decision_id) REFERENCES Decisions (decision_id)      DESTINATION KEY (outcome_id) REFERENCES Outcomes (outcome_id)  );<\/code>
<\/a><\/div><\/pre>\n
\u0417\u0430\u043f\u0440\u043e\u0441\u044b \u2014 \u043d\u0430 GQL, \u0434\u0435\u043a\u043b\u0430\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u043c \u044f\u0437\u044b\u043a\u0435 \u043f\u0430\u0442\u0442\u0435\u0440\u043d-\u043c\u0430\u0442\u0447\u0438\u043d\u0433\u0430:<\/p>\n
GRAPH MarketingContextGraphMATCH (c:Customers {industry: $1, tier: $2})<-[:AboutCustomer]-(d:Decisions {signal_type: $3})-[:ResultedIn]->(o:Outcomes)WHERE o.result = 'Renewed'RETURN d.decision_type AS Action_Type, d.reasoning_text AS Success_LogicORDER BY d.timestamp DESCLIMIT 3<\/code>
<\/a><\/div><\/pre>\n
\u041a\u0440\u0430\u0441\u0438\u0432\u043e. \u041b\u0430\u043a\u043e\u043d\u0438\u0447\u043d\u043e. \u041f\u0430\u0442\u0442\u0435\u0440\u043d-\u043c\u0430\u0442\u0447\u0438\u043d\u0433: \u00ab\u043d\u0430\u0439\u0434\u0438 \u043c\u043d\u0435 \u0432\u0441\u0435 \u043f\u0443\u0442\u0438 Decision \u2192 Customer \u2190 Decision \u2192 Outcome, \u0433\u0434\u0435 outcome = Renewed\u00bb. \u041e\u0434\u043d\u0430 \u043a\u043e\u043d\u0441\u0442\u0440\u0443\u043a\u0446\u0438\u044f MATCH<\/code> \u0437\u0430\u043c\u0435\u043d\u044f\u0435\u0442 20 \u0441\u0442\u0440\u043e\u043a SQL-\u0434\u0436\u043e\u0439\u043d\u043e\u0432.<\/p>\n
AlloyDB: \u0440\u0435\u043a\u0443\u0440\u0441\u0438\u0432\u043d\u044b\u0435 CTE<\/h4>\n
\u0423 \u043d\u0430\u0441 \u043d\u0435\u0442 GQL. \u041d\u043e \u0435\u0441\u0442\u044c WITH RECURSIVE<\/code> \u2014 \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u044b\u0439 \u043c\u0435\u0445\u0430\u043d\u0438\u0437\u043c PostgreSQL \u0434\u043b\u044f \u043e\u0431\u0445\u043e\u0434\u0430 \u0433\u0440\u0430\u0444\u043e\u0432:<\/p>\n
CREATE OR REPLACE FUNCTION graph_neighbors(  _label TEXT, _kind TEXT DEFAULT NULL,  _depth INT DEFAULT 1, _limit INT DEFAULT 20)RETURNS TABLE (node_label TEXT, node_kind TEXT, edge_kind TEXT, depth INT, wiki_path TEXT) AS $$WITH RECURSIVE traverse(id, node_label, node_kind, wiki_path, edge_kind, depth) AS (  -- Anchor: \u0441\u0442\u0430\u0440\u0442\u043e\u0432\u0430\u044f \u043d\u043e\u0434\u0430  SELECT n.id, n.label, n.kind, n.wiki_path, NULL, 0  FROM graph_nodes n WHERE n.label = _label  UNION ALL  -- Recursive step: \u043e\u0431\u0445\u043e\u0434 \u043f\u043e \u0440\u0451\u0431\u0440\u0430\u043c \u0432 \u043e\u0431\u0435 \u0441\u0442\u043e\u0440\u043e\u043d\u044b  SELECT COALESCE(t.id, s.id), COALESCE(t.label, s.label),         COALESCE(t.kind, s.kind), COALESCE(t.wiki_path, s.wiki_path),         e.kind, p.depth + 1  FROM traverse p  JOIN graph_edges e ON (e.source_id = p.id OR e.target_id = p.id)  LEFT JOIN graph_nodes t ON t.id = e.target_id AND e.source_id = p.id  LEFT JOIN graph_nodes s ON s.id = e.source_id AND e.target_id = p.id  WHERE p.depth < _depth    AND (COALESCE(t.id, s.id) IS NOT NULL)    AND (_kind IS NULL OR e.kind = _kind))SELECT DISTINCT ON (node_label) node_label, node_kind, edge_kind, depth, wiki_pathFROM traverse WHERE depth > 0 ORDER BY node_label, depth LIMIT _limit;$$ LANGUAGE sql STABLE;<\/code>
<\/a><\/div><\/pre>\n
\u0413\u0440\u043e\u043c\u043e\u0437\u0434\u043a\u043e? \u0414\u0430. \u041d\u043e \u0440\u0430\u0431\u043e\u0442\u0430\u0435\u0442.<\/p>\n\n
\u041f\u043e\u0447\u0435\u043c\u0443 \u043c\u044b \u043e\u0441\u0442\u0430\u043b\u0438\u0441\u044c \u043d\u0430 SQL: \u043f\u044f\u0442\u044c \u043f\u0440\u0438\u0447\u0438\u043d<\/h3>\n
1. \u041e\u0434\u043d\u0430 \u0431\u0430\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u0432\u043c\u0435\u0441\u0442\u043e \u0434\u0432\u0443\u0445<\/h4>\n
\u0412 \u043d\u0430\u0448\u0435\u043c AlloyDB \u0443\u0436\u0435 \u0436\u0438\u0432\u0443\u0442:<\/p>\n