我正在memsql上尝试TPCH DDL查询。我是新来的。我无法将5个TPCH ddl sql查询转换为memsql查询。无法使用memsql的FOREIGH SHARD key概念实现外键关系。请帮助我将下面8个表创建查询中的5个转换为memsql查询。努力尝试,但面临很多不同的问题。
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL PRIMARY KEY,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152)
);
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL PRIMARY KEY,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL REFERENCES REGION(R_REGIONKEY),
N_COMMENT VARCHAR(152)
);
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL PRIMARY KEY,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL
);
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL PRIMARY KEY,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL REFERENCES NATION(N_NATIONKEY),
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL
);
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL REFERENCES PART(P_PARTKEY),
PS_SUPPKEY INTEGER NOT NULL REFERENCES SUPPLIER(S_SUPPKEY),
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL,
PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
);
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL PRIMARY KEY,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL REFERENCES NATION(N_NATIONKEY),
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL
);
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL PRIMARY KEY,
O_CUSTKEY INTEGER NOT NULL REFERENCES CUSTOMER(C_CUSTKEY),
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL
);
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL REFERENCES ORDERS(O_ORDERKEY),
L_PARTKEY INTEGER NOT NULL REFERENCES PART(P_PARTKEY),
L_SUPPKEY INTEGER NOT NULL REFERENCES SUPPLIER(S_SUPPKEY),
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL,
PRIMARY KEY (L_ORDERKEY,L_LINENUMBER),
FOREIGN KEY (L_PARTKEY,L_SUPPKEY) REFERENCES PARTSUPP(PS_PARTKEY, PS_SUPPKEY)
);
我可以在memsql中创建前3个表,但不能创建其余的表。第一个和第三个查询非常简单,并且可以正常工作。我可以创建第二个表,但再次不确定这是否是正确的方式来实现。
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152),
FOREIGN SHARD KEY (N_REGIONKEY) REFERENCES REGION (R_REGIONKEY),
PRIMARY KEY (N_NATIONKEY, N_REGIONKEY)
);
是否可以在memsql中只创建复制表而不创建分区?怎么做?
最佳答案
由于MemSQL不支持引用完整性,因此外部shard键是一种优化辅助工具,而不是必需的。但是,外部碎片密钥确实允许您在表创建时知道两个表可以在该密钥上本地连接(没有网络通信)。但是,优化器不需要外部shard键来利用此数据区域性。
从ORDERS
和LINEITEM
表开始:
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL PRIMARY KEY,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL,
KEY (O_CUSTKEY)
);
CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL,
PRIMARY KEY (L_ORDERKEY,L_LINENUMBER),
FOREIGN SHARD KEY (L_ORDERKEY) REFERENCES ORDERS (O_ORDERKEY),
KEY (L_PARTKEY),
KEY (L_SUPPKEY)
);
在这种情况下,我们知道可以利用
ORDERS
和LINEITEM
之间的本地连接,因为它们都在ORDERKEY
上分片。ORDERS
和LINEITEM
是TPCH中最大的两个表,因此我们希望确保它们可以在本地连接。因为主键是ORDERS
,所以我不需要为O_ORDERKEY
指定shard键。MemSQL将自动切分ORDERS
。我还在其余的外键列上放置了辅助索引。这很有用,因为外键上会有连接。
将这些概念应用于
O_ORDERKEY
、PART
、PARTSUPP
和SUPPLIER
:CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL PRIMARY KEY,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL,
KEY(C_NATIONKEY)
);
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL PRIMARY KEY,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL,
KEY(S_NATIONKEY)
);
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL PRIMARY KEY,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL
);
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL,
PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
SHARD KEY(PS_PARTKEY),
KEY(PS_SUPPKEY)
);
虽然
CUSTOMER
和PARTSUPP
都是在同一个键(PARKEY)上分割的,但我不需要指定一个外来的碎片键来利用它们之间的本地连接,优化器会自动拾取。为了回答您的最后一个问题,MemSQL允许您创建一个复制表,而不是一个分区表。这称为areference table,对于
PART
和NATION
表非常有用,因为它们非常小。引用表不是运行分布式查询所必需的,但却是一种有用的优化。CREATE REFERENCE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL PRIMARY KEY,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152)
);
CREATE REFERENCE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL PRIMARY KEY,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152)
);
有关所述内容的更多文档,请查看:
http://docs.memsql.com/latest/concepts/distributed_sql/