查询Impala JDBCDriver时出错

Question

你好，我有一个奇怪的行为与查询。查询工作正常，当它在WebSQL中执行时，但是我得到Impala JDBCDriver的以下错误:

查询:[SmOpenAsns] ->[Cloudera]ImpalaJDBCDriver处理查询/语句出错错误码:0,SQL状态:TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:ParseException:第一行语法错误:．．．．asn_id AS order_id FROM ${siteidlowercase}.f_receivin…

遇到:意外字符预期:Add, alter, and, array, as, asc, between, bigint, binary, block_size, boolean, cached, cascade, change, char, comment, compression, cross, date, datetime, decimal, default, desc,div, real, drop, else, encoding, end, float, following, from, full, group, ignore, having, ilike, in, inner, integer, iregexp, is, join, left, like, limit, location, map, not, null, nulls, offset, on, or, order, partition, partitioned, prior, primary, purge, range, recover, regexp, rename, replace, restrict, right，like, ROW, ROWS, SELECT, SET, SMALLINT, SORT, STORED, straight - join, STRING, STRUCT, tablessample, TBLPROPERTIES, THEN, TIMESTAMP, TINYINT, TO, UNCACHED, UNION, USING, VALUES, VARCHAR, WHEN, WHERE, WITH，逗号，IDENTIFIER

CAUSED BY: Exception:语法错误)，查询sm_orders_total作为(SELECT CASE WHEN CONCAT(SUBSTR(fact.))。creation_time, 1, 10)， ' '， SUBSTR(creation_time, 12,8))>= CONCAT(from_timestamp(DAYS_ADD(now()， -1)， 'yyyy-MM-dd')， ' '， '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact. time))creation_time, 1,10)， 'yyyy-MM-dd')， 'dd.MM.yyyy')creation_time, 1, 10)， ' '， SUBSTR(creation_time, 12,8))>= CONCAT(from_timestamp(DAYS_ADD(now()， -2)， 'yyyy-MM-dd')， ' '， '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact. time))creation_time, 1,10)， 'yyyy-MM-dd')， 'dd.MM.yyyy')creation_time, 1, 10)， ' '， SUBSTR(creation_time, 12,8)) <= concat(from_timestamp(DAYS_ADD(now()， -2)， 'yyyy-MM-dd')， ' '， '23:00:00') THEN 'Older' END AS creation_date, fact。asn_id AS order_id FROM ${siteidlowercase}。f_receiving fact JOIN ${siteidlowercase}。d_wms_cust_article art关于美术。Article_id =事实。article_id WHERE fact。po_number是NULL和fact。='RETURN' AND fact。asn_status in ('LOADED'， 'OPEN'， 'RECEIVING'， 'REGISTERED') GROUP BY fact。asn_id, fact. create_time)， sm_orders_frozen AS (SELECT CASE WHEN CONCAT(SUBSTR(fact. creation_time))creation_time, 1, 10)， ' '， SUBSTR(creation_time, 12,8))>= CONCAT(from_timestamp(DAYS_ADD(now()， -1)， 'yyyy-MM-dd')， ' '， '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact. time))creation_time, 1,10)， 'yyyy-MM-dd')， 'dd.MM.yyyy')creation_time, 1, 10)， ' '， SUBSTR(creation_time, 12,8))>= CONCAT(from_timestamp(DAYS_ADD(now()， -2)， 'yyyy-MM-dd')， ' '， '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact. time))creation_time, 1,10)， 'yyyy-MM-dd')， 'dd.MM.yyyy')creation_time, 1, 10)， ' '， SUBSTR(creation_time, 12,8)) <= concat(from_timestamp(DAYS_ADD(now()， -2)， 'yyyy-MM-dd')， ' '， '23:00:00') THEN 'Older' END AS creation_date, fact。asn_id AS order_id FROM ${siteidlowercase}。f_receiving fact JOIN ${siteidlowercase}。d_wms_cust_article art关于美术。Article_id =事实。article_id WHERE fact。po_number是NULL和fact。='RETURN' AND fact。asn_status in ('LOADED'， 'OPEN'， 'RECEIVING'， 'REGISTERED')和art。classification_name类似于"frozen%"。asn_id, fact.creation_time) SELECT sm_orders_total。creation_date sm_creation_date, COUNT(DISTINCT sm_orders_frozen.order_id) sm_frozen_asns, COUNT(DISTINCT sm_orders_total.order_id) sm_total_asns FROM sm_orders_total JOIN sm_orders_frozen ON sm_orders_total. COUNT(DISTINCT sm_orders_total.order_id)Creation_date = sm_orders_total。creation_date GROUP BY sm_creation_date ORDER BY split_part(SUBSTR(sm_orders_total.))Creation_date, 1,10)， ';’，2)desc, split_part(SUBSTR(sm_orders_total.)Creation_date, 1,10)， ';'， 1) desc LIMIT 1000000/* [SmOpenAsns] */.

查询:

WITH sm_orders_total AS(
SELECT 
CASE 
WHEN CONCAT(SUBSTR(fact.creation_time, 1, 10), ' ', SUBSTR(fact.creation_time, 12, 8)) >= CONCAT(from_timestamp(DAYS_ADD(now(), -1), 'yyyy-MM-dd'), ' ', '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact.creation_time, 1, 10), 'yyyy-MM-dd'), 'dd.MM.yyyy') 
WHEN CONCAT(SUBSTR(fact.creation_time, 1, 10), ' ', SUBSTR(fact.creation_time, 12, 8)) >= CONCAT(from_timestamp(DAYS_ADD(now(), -2), 'yyyy-MM-dd'), ' ', '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact.creation_time, 1, 10), 'yyyy-MM-dd'), 'dd.MM.yyyy') 
WHEN CONCAT(SUBSTR(fact.creation_time, 1, 10), ' ', SUBSTR(fact.creation_time, 12, 8)) <= concat(from_timestamp(DAYS_ADD(now(), -2), 'yyyy-MM-dd'), ' ', '23:00:00') THEN 'Older' 
END AS creation_date, 
fact.asn_id AS order_id 
FROM ${siteidlowercase}.f_receiving fact 
JOIN ${siteidlowercase}.d_wms_cust_article art ON art.article_id = fact.article_id 
WHERE fact.po_number is NULL 
AND fact.asn_type !='RETURN' 
AND fact.asn_status in ('LOADED', 'OPEN', 'RECEIVING', 'REGISTERED') 
GROUP BY fact.asn_id , fact.creation_time), 
sm_orders_frozen AS (
SELECT 
CASE 
WHEN CONCAT(SUBSTR(fact.creation_time, 1, 10), ' ', SUBSTR(fact.creation_time, 12, 8)) >= CONCAT(from_timestamp(DAYS_ADD(now(), -1), 'yyyy-MM-dd'), ' ', '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact.creation_time, 1, 10), 'yyyy-MM-dd'), 'dd.MM.yyyy') 
WHEN CONCAT(SUBSTR(fact.creation_time, 1, 10), ' ', SUBSTR(fact.creation_time, 12, 8)) >= CONCAT(from_timestamp(DAYS_ADD(now(), -2), 'yyyy-MM-dd'), ' ', '23:00:00') THEN from_timestamp(to_timestamp(SUBSTR(fact.creation_time, 1, 10), 'yyyy-MM-dd'), 'dd.MM.yyyy') 
WHEN CONCAT(SUBSTR(fact.creation_time, 1, 10), ' ', SUBSTR(fact.creation_time, 12, 8)) <= concat(from_timestamp(DAYS_ADD(now(), -2), 'yyyy-MM-dd'), ' ', '23:00:00') THEN 'Older' 
END AS creation_date, 
fact.asn_id AS order_id 
FROM ${siteidlowercase}.f_receiving fact 
JOIN ${siteidlowercase}.d_wms_cust_article art ON art.article_id = fact.article_id 
WHERE fact.po_number is NULL 
AND fact.asn_type !='RETURN' 
AND fact.asn_status in ('LOADED', 'OPEN', 'RECEIVING', 'REGISTERED') 
AND art.classification_name like 'frozen%' 
GROUP BY fact.asn_id, fact.creation_time) 
SELECT 
sm_orders_total.creation_date sm_creation_date, 
COUNT(DISTINCT sm_orders_frozen.order_id) sm_frozen_asns, 
COUNT(DISTINCT sm_orders_total.order_id) sm_total_asns 
FROM sm_orders_total 
JOIN sm_orders_frozen ON sm_orders_frozen.creation_date = sm_orders_total.creation_date 
GROUP BY sm_creation_date 
ORDER BY split_part(SUBSTR(sm_orders_total.creation_date, 1, 10), '.', 2) desc, split_part(SUBSTR(sm_orders_total.creation_date, 1, 10), '.', 1) desc;

我在查询中找不到任何错误。我不知道有什么问题。谢谢你。

Answer 1

我在查询中发现了错误。

实际上是参数写错了。参数完全用小写写成，除了"L"需要大写

错:

${siteidlowercase}.f_receiving fact 

正确的:

${siteidLowercase}.f_receiving fact 

现在一切正常。