%load_ext sql
%sql postgresql://postgres:changeme@agensgraph_postgres_container/postgres
'Connected: postgres@postgres'
%%sql
SELECT * from pg_database
* postgresql://postgres:***@agensgraph_postgres_container/postgres 3 rows affected.
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl |
---|---|---|---|---|---|---|---|---|---|---|---|---|
postgres | 10 | 6 | en_US.utf8 | en_US.utf8 | False | True | -1 | 13066 | 562 | 1 | 1663 | None |
template1 | 10 | 6 | en_US.utf8 | en_US.utf8 | True | True | -1 | 13066 | 562 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} |
template0 | 10 | 6 | en_US.utf8 | en_US.utf8 | True | False | -1 | 13066 | 562 | 1 | 1663 | {=c/postgres,postgres=CTc/postgres} |
%sql postgresql://agens:agens@agensgraph_agens_container/postgres
'Connected: agens@postgres'
%%sql
SELECT * from pg_database
* postgresql://agens:***@agensgraph_container/postgres postgresql://postgres:***@postgres_container/postgres 4 rows affected.
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl |
---|---|---|---|---|---|---|---|---|---|---|---|---|
postgres | 10 | 0 | C | C | False | True | -1 | 12420 | 550 | 1 | 1663 | None |
agens | 10 | 0 | C | C | False | True | -1 | 12420 | 550 | 1 | 1663 | None |
template1 | 10 | 0 | C | C | True | True | -1 | 12420 | 550 | 1 | 1663 | {=c/agens,agens=CTc/agens} |
template0 | 10 | 0 | C | C | True | False | -1 | 12420 | 550 | 1 | 1663 | {=c/agens,agens=CTc/agens} |
%sql postgresql://agens:agens@agensgraph_container/agens
'Connected: agens@agens'
%%sql
SELECT * FROM pg_catalog.pg_tables WHERE schemaname !='pg_catalog' AND schemaname !='information_schema';
* postgresql://agens:***@agensgraph_container/agens postgresql://agens:***@agensgraph_container/postgres postgresql://postgres:***@postgres_container/postgres 2 rows affected.
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity |
---|---|---|---|---|---|---|---|
agens_graph | ag_vertex | agens | None | True | False | False | False |
agens_graph | ag_edge | agens | None | True | False | False | False |
%%sql
CREATE EXTENSION IF NOT EXISTS file_fdw;
* postgresql://agens:***@agensgraph_container/agens postgresql://agens:***@agensgraph_container/postgres postgresql://postgres:***@postgres_container/postgres Done.
[]
%%sql
CREATE SERVER northwind FOREIGN DATA WRAPPER file_fdw;
* postgresql://agens:***@agensgraph_container/agens postgresql://agens:***@agensgraph_container/postgres postgresql://postgres:***@postgres_container/postgres Done.
[]
In the tutorial, paths are set to D:\northwind\
that need changing, eg to /home/agens/AgensGraph/shareddata
(the path the data was mounted to in the setup). Note that the path is the path inside the agens
container. that For convenience, we also mount that volume across to the notebook /home/jovyan/agens
directory so that we can pass files from the notebook container to the agens
container.
#The path is path we mounted the data onto in the agensgraph container
PATH = '/home/agens/AgensGraph/shareddata'
setup='''
-- Go defensive
DROP FOREIGN TABLE IF EXISTS categories;
DROP FOREIGN TABLE IF EXISTS customers;
DROP FOREIGN TABLE IF EXISTS employees;
DROP FOREIGN TABLE IF EXISTS employee_territories;
DROP FOREIGN TABLE IF EXISTS orders_details;
DROP FOREIGN TABLE IF EXISTS orders;
DROP FOREIGN TABLE IF EXISTS products;
DROP FOREIGN TABLE IF EXISTS regions;
DROP FOREIGN TABLE IF EXISTS shippers;
DROP FOREIGN TABLE IF EXISTS suppliers;
DROP FOREIGN TABLE IF EXISTS territories;
CREATE FOREIGN TABLE categories (
CategoryID int,
CategoryName varchar(15),
Description text,
Picture bytea
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/categories.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE customers (
CustomerID char(5),
CompanyName varchar(40),
ContactName varchar(30),
ContactTitle varchar(30),
Address varchar(60),
City varchar(15),
Region varchar(15),
PostalCode varchar(10),
Country varchar(15),
Phone varchar(24),
Fax varchar(24)
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/customers.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE employees (
EmployeeID int,
LastName varchar(20),
FirstName varchar(10),
Title varchar(30),
TitleOfCourtesy varchar(25),
BirthDate date,
HireDate date,
Address varchar(60),
City varchar(15),
Region varchar(15),
PostalCode varchar(10),
Country varchar(15),
HomePhone varchar(24),
Extension varchar(4),
Photo bytea,
Notes text,
ReportTo int,
PhotoPath varchar(255)
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/employees.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE employee_territories (
EmployeeID int,
TerritoryID varchar(20)
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/employee_territories.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE orders_details (
orderID int,
ProductID int,
UnitPrice money,
Quantity smallint,
Discount real
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/orders_details.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE orders (
orderID int,
CustomerID char(5),
EmployeeID int,
orderDate date,
RequiredDate date,
ShippedDate date,
ShipVia int,
Freight money,
ShipName varchar(40),
ShipAddress varchar(60),
ShipCity varchar(15),
ShipRegion varchar(15),
ShipPostalCode varchar(10),
ShipCountry varchar(15)
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/orders.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE products (
ProductID int,
ProductName varchar(40),
SupplierID int,
CategoryID int,
QuantityPerUnit varchar(20),
UnitPrice money,
UnitsInStock smallint,
UnitsOnorder smallint,
ReorderLevel smallint,
Discontinued bit
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/products.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE regions (
RegionID int,
RegionDescription char(50)
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/regions.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE shippers (
ShipperID int,
CompanyName varchar(40),
Phone varchar(24)
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/shippers.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE suppliers (
SupplierID int,
CompanyName varchar(40),
ContactName varchar(30),
ContactTitle varchar(30),
Address varchar(60),
City varchar(15),
Region varchar(15),
PostalCode varchar(10),
Country varchar(15),
Phone varchar(24),
Fax varchar(24),
HomePage text
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/suppliers.csv', delimiter ',', quote '"', null '');
CREATE FOREIGN TABLE territories (
TerritoryID varchar(20),
TerritoryDescription char(50),
RegionID int
)
SERVER northwind
OPTIONS (FORMAT 'csv', HEADER 'true', FILENAME '{path}/territories.csv', delimiter ',', quote '"', null '');
'''.format(path=PATH)
%sql $setup
* postgresql://agens:***@agensgraph_container/agens postgresql://agens:***@agensgraph_container/postgres postgresql://postgres:***@postgres_container/postgres Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done. Done.
[]
%%sql
-- Go defensive
DROP GRAPH IF EXISTS northwind_graph CASCADE;
CREATE GRAPH northwind_graph;
SET graph_path = northwind_graph;
* postgresql://agens:***@agensgraph_container/agens postgresql://agens:***@agensgraph_container/postgres postgresql://postgres:***@postgres_container/postgres Done. Done. Done.
[]
Loading the data in requires the data to be mounted in the database container (unless we can help psycopg2
load files stored in the notebook container into the agensgraph
container?
%%sql
LOAD FROM categories AS source CREATE (n:category=to_jsonb(source));
LOAD FROM customers AS source CREATE (n:customer=to_jsonb(source));
LOAD FROM employees AS source CREATE (n:employee=to_jsonb(source));
create vlabel if not exists "order";
LOAD FROM orders AS source CREATE (n:"order"=to_jsonb(source));
LOAD FROM products AS source CREATE (n:product=to_jsonb(source));
LOAD FROM regions AS source CREATE (n:region=to_jsonb(source));
LOAD FROM shippers AS source CREATE (n:shipper=to_jsonb(source));
LOAD FROM suppliers AS source CREATE (n:supplier=to_jsonb(source));
LOAD FROM territories AS source CREATE (n:territory=to_jsonb(source));
* postgresql://agens:***@agensgraph_container/agens postgresql://agens:***@agensgraph_container/postgres postgresql://postgres:***@postgres_container/postgres Done. Done. Done. Done. Done. Done. Done. Done. Done. Done.
[]