use tpchtxt;

create external table lineitem (
    l_orderkey int,
    l_partkey int,
    l_suppkey int,
    l_linenumber int,
    l_quantity double,
    l_extendedprice double,
    l_discount double,
    l_tax double,
    l_returnflag string,
    l_linestatus string,
    l_shipdate string,
    l_commitdate string,
    l_receiptdate string,
    l_shipinstruct string,
    l_shipmode string,
    l_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/lineitem';
LOAD DATA LOCAL INPATH 'file:///tmp/data/lineitem.tbl' INTO TABLE lineitem;

create external table nation (
  n_nationkey int,
  n_name string,
  n_regionkey int,
  n_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/nation';
LOAD DATA LOCAL INPATH 'file:///tmp/data/nation.tbl' INTO TABLE nation;

create external table region (
  r_regionkey int,
  r_name string,
  r_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/region';
LOAD DATA LOCAL INPATH 'file:///tmp/data/region.tbl' INTO TABLE region;

create external table part (
  p_partkey int,
  p_name string,
  p_mfgr string,
  p_brand string,
  p_type string,
  p_size int,
  p_container string,
  p_retailprice double,
  p_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/part';
LOAD DATA LOCAL INPATH 'file:///tmp/data/part.tbl' INTO TABLE part;

create external table supplier (
  s_suppkey int,
  s_name string,
  s_address string,
  s_nationkey int,
  s_phone string,
  s_acctbal double,
  s_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/supplier';
LOAD DATA LOCAL INPATH 'file:///tmp/data/supplier.tbl' INTO TABLE supplier;

create external table partsupp (
  ps_partkey int,
  ps_suppkey int,
  ps_availqty int,
  ps_supplycost double,
  ps_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/partsupp';
LOAD DATA LOCAL INPATH 'file:///tmp/data/partsupp.tbl' INTO TABLE partsupp;

create external table customer (
  c_custkey int,
  c_name string,
  c_address string,
  c_nationkey int,
  c_phone string,
  c_acctbal double,
  c_mktsegment string,
  c_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/customer';
LOAD DATA LOCAL INPATH 'file:///tmp/data/customer.tbl' INTO TABLE customer;

create external table orders (
  o_orderkey int,
  o_custkey int,
  o_orderstatus string,
  o_totalprice double,
  o_orderdate date,
  o_orderpriority string,
  o_clerk string,
  o_shippriority int,
  o_comment string
)
row format delimited
fields terminated by '|'
stored as textfile
location 'file:///opt/data/spark/tpchtxt/orders';
LOAD DATA LOCAL INPATH 'file:///tmp/data/customer.tbl' INTO TABLE orders;