Hi,
My Spark version is v1.1.0 and Hive is 0.12.0, I need to use more than 1
subquery in my Spark SQL, below are my sample table structures and a SQL that
contains more than 1 subquery.
Question 1: How to load a HIVE table into Scala/Spark?
Question 2: How to implement a SQL_WITH_MORE_THAN_ONE_SUBQUERY in SCALA/SPARK?
Question 3: What is the DATEADD function in Scala/Spark? or how to implement
"DATEADD(MONTH, 3, '2013-07-01')” and "DATEADD(YEAR, 1, '2014-01-01')” in Spark
or Hive?
I can find HIVE (date_add(string startdate, int days)) but it is in days not
MONTH / YEAR.
Thanks.
Regards
Arthur
===
My sample SQL with more than 1 subquery:
SELECT S_NAME,
COUNT(*) AS NUMWAIT
FROM SUPPLIER,
LINEITEM L1,
ORDERS
WHERE S_SUPPKEY = L1.L_SUPPKEY
AND O_ORDERKEY = L1.L_ORDERKEY
AND O_ORDERSTATUS = 'F'
AND L1.L_RECEIPTDATE > L1.L_COMMITDATE
AND EXISTS (SELECT *
FROM LINEITEM L2
WHERE L2.L_ORDERKEY = L1.L_ORDERKEY
AND L2.L_SUPPKEY <> L1.L_SUPPKEY)
AND NOT EXISTS (SELECT *
FROM LINEITEM L3
WHERE L3.L_ORDERKEY = L1.L_ORDERKEY
AND L3.L_SUPPKEY <> L1.L_SUPPKEY
AND L3.L_RECEIPTDATE > L3.L_COMMITDATE)
GROUP BY S_NAME
ORDER BY NUMWAIT DESC, S_NAME
limit 100;
===
Supplier Table:
CREATE TABLE IF NOT EXISTS SUPPLIER (
S_SUPPKEY INTEGER PRIMARY KEY,
S_NAME CHAR(25),
S_ADDRESS VARCHAR(40),
S_NATIONKEY BIGINT NOT NULL,
S_PHONE CHAR(15),
S_ACCTBAL DECIMAL,
S_COMMENT VARCHAR(101)
)
===
Order Table:
CREATE TABLE IF NOT EXISTS ORDERS (
O_ORDERKEY INTEGER PRIMARY KEY,
O_CUSTKEY BIGINT NOT NULL,
O_ORDERSTATUS CHAR(1),
O_TOTALPRICE DECIMAL,
O_ORDERDATE CHAR(10),
O_ORDERPRIORITY CHAR(15),
O_CLERK CHAR(15),
O_SHIPPRIORITY INTEGER,
O_COMMENT VARCHAR(79)
===
LineItem Table:
CREATE TABLE IF NOT EXISTS LINEITEM (
L_ORDERKEY BIGINT not null,
L_PARTKEY BIGINT,
L_SUPPKEY BIGINT,
L_LINENUMBER INTEGER not null,
L_QUANTITY DECIMAL,
L_EXTENDEDPRICE DECIMAL,
L_DISCOUNT DECIMAL,
L_TAX DECIMAL,
L_SHIPDATE CHAR(10),
L_COMMITDATE CHAR(10),
L_RECEIPTDATE CHAR(10),
L_RETURNFLAG CHAR(1),
L_LINESTATUS CHAR(1),
L_SHIPINSTRUCT CHAR(25),
L_SHIPMODE CHAR(10),
L_COMMENT VARCHAR(44),
CONSTRAINT pk PRIMARY KEY (L_ORDERKEY, L_LINENUMBER )
)