在impala db中创建表作为select percentage子查询

mpgws1up 于 2021-05-31 发布在 Hadoop

关注(0)|答案(2)|浏览(320)

我是impala的新手，我需要用select resultset创建表，而且这个sql是用jdbc在java中运行的，请看下面的查询：

create table if not exists my_temp_table as select 
41 as rule_id,49 as record_id,
(select count(1) as val from dirty_table where msg regexp '^[1]([3-9])[0-9]{9}$' )/(select count(1) from dirty_table);

我需要创建表 my_temp_table 在这个表中插入数据，这是我需要运行的一个sql。但运行失败，错误如下：

[HY000][500051] [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:ParseException: Syntax error

经过检查，我知道 Impala 不支持 SELECT 子句子查询，只能在中使用子查询 FROM 或者 WHERE 条款，见impala文件：https://impala.apache.org/docs/build/html/topics/impala_subqueries.html.
所以对于这个问题我该怎么做才能解决这个问题。
我的想法：
更新sql让它执行，我试过了 WITH 就像下面的sql一样，它可以工作，但不能在 CREATE TABLE ... AS ... .

WITH q1 AS (
      select count(1) as val from dirty_table where msg regexp '^[1]([3-9])[0-9]{9}$'
    ),
    q2 AS (
      select count(1) val2 from dirty_table
    )
    SELECT 100 * q1.val / q2.val2  result
    FROM q1, q2

或者，有这样的说法吗 BEGIN ... END 在mysql或oracle中，我可以分别运行这个sql。

Java sql hadoop impala count

来源：https://stackoverflow.com/questions/63116374/create-table-as-select-percentage-subquery-in-impala-db

2条答案

按热度按时间

e3bfsja21#

我认为条件平均法可以简单有效地完成你想要的，只需一次表扫描：

select avg(case when msg regexp '^[1]([3-9])[0-9]{9}$' then 100.0 else 0 end) result
from dirty_table

你可以把这个变成一个 create table 声明：

create table my_temp_table as
select avg(case when msg regexp '^[1]([3-9])[0-9]{9}$' then 100.0 else 0 end) result
from dirty_table

赞(0）回复(0）举报 2021-05-31

mqkwyuun2#

以你的例子，我会尝试这些方法，我相信，可以很好地工作。我和 Impala 核对了溶液

CREATE TABLE dirty_table (
 id INT,
 msg STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED  BY ','
STORED AS TEXTFILE;

[localhost.localdomain:21000] > SELECT * FROM dirty_table;
Query: SELECT * FROM dirty_table
Query submitted at: 2020-07-28 17:05:24 (Coordinator: http://localhost.localdomain:25000)
Query progress can be monitored at: http://localhost.localdomain:25000/query_plan?query_id=5441d6a46ce61e7b:8e49432600000000
+----+-------------+
| id | msg         |
+----+-------------+
| 1  | 13321512121 |
| 2  | 13121212121 |
| 3  | 03121212121 |
| 4  | 13321512121 |
| 5  | 13121212121 |
| 6  | 03121212121 |
| 7  | 13121212121 |
+----+-------------+
Fetched 7 row(s) in 0.14s

第一个例子

CREATE TABLE IF NOT EXISTS my_temp_table AS
SELECT 41 AS rule_id, 49 AS record_id, val1 / val2 AS result
FROM (SELECT COUNT(1) AS val1 FROM dirty_table WHERE msg regexp '^[1]([3-9])[0-9]{9}$' ) a,
     (SELECT COUNT(1) AS val2 FROM dirty_table) b;

[localhost.localdomain:21000] > CREATE TABLE IF NOT EXISTS my_temp_table AS
                              > SELECT 41 AS rule_id, 49 AS record_id, val1 / val2 AS result
                              > FROM (SELECT COUNT(1) AS val1 FROM dirty_table WHERE msg regexp '^[1]([3-9])[0-9]{9}$' ) a,
                              >      (SELECT COUNT(1) AS val2 FROM dirty_table) b;
Query: CREATE TABLE IF NOT EXISTS my_temp_table AS
SELECT 41 AS rule_id, 49 AS record_id, val1 / val2 AS result
FROM (SELECT COUNT(1) AS val1 FROM dirty_table WHERE msg regexp '^[1]([3-9])[0-9]{9}$' ) a,
     (SELECT COUNT(1) AS val2 FROM dirty_table) b
+-------------------+
| summary           |
+-------------------+
| Inserted 0 row(s) |
+-------------------+
Fetched 1 row(s) in 0.21s

[localhost.localdomain:21000] > invalidate metadata;

[localhost.localdomain:21000] > SELECT * FROM my_temp_table;
Query: select * from my_temp_table
Query submitted at: 2020-07-28 17:03:44 (Coordinator: http://localhost.localdomain:25000)
Query progress can be monitored at: http://localhost.localdomain:25000/query_plan?query_id=47370bf793a09b:29c4dfa000000000
+---------+-----------+--------------------+
| rule_id | record_id | result             |
+---------+-----------+--------------------+
| 41      | 49        | 0.7142857142857143 |
+---------+-----------+--------------------+
Fetched 1 row(s) in 0.13s

第二个例子

DROP TABLE my_temp_table;

CREATE TABLE IF NOT EXISTS my_temp_table AS 
SELECT result FROM
    (WITH q1 AS (
      SELECT COUNT(1) AS val FROM dirty_table WHERE msg regexp '^[1]([3-9])[0-9]{9}$'
    ),
    q2 AS (
      SELECT COUNT(1) val2 FROM dirty_table
    )
    SELECT 100 * q1.val / q2.val2 AS result
    FROM q1, q2) t;

[localhost.localdomain:21000] > CREATE TABLE IF NOT EXISTS my_temp_table AS 
                              > SELECT result FROM
                              >     (WITH q1 AS (
                              >       SELECT COUNT(1) AS val FROM dirty_table WHERE msg regexp '^[1]([3-9])[0-9]{9}$'
                              >     ),
                              >     q2 AS (
                              >       SELECT COUNT(1) val2 FROM dirty_table
                              >     )
                              >     SELECT 100 * q1.val / q2.val2 AS result
                              >     FROM q1, q2) t;
Query: CREATE TABLE IF NOT EXISTS my_temp_table AS
SELECT result FROM
    (WITH q1 AS (
      SELECT COUNT(1) AS val FROM dirty_table WHERE msg regexp '^[1]([3-9])[0-9]{9}$'
    ),
    q2 AS (
      SELECT COUNT(1) val2 FROM dirty_table
    )
    SELECT 100 * q1.val / q2.val2 AS result
    FROM q1, q2) t
+-------------------+
| summary           |
+-------------------+
| Inserted 1 row(s) |
+-------------------+
Fetched 1 row(s) in 0.40s

[localhost.localdomain:21000] > invalidate metadata;

[localhost.localdomain:21000] > SELECT * FROM my_temp_table;
Query: SELECT * FROM my_temp_table
Query submitted at: 2020-07-28 17:08:17 (Coordinator: http://localhost.localdomain:25000)
Query progress can be monitored at: http://localhost.localdomain:25000/query_plan?query_id=3447684ef59d0c4:f70779200000000
+-------------------+
| result            |
+-------------------+
| 71.42857142857143 |
+-------------------+
Fetched 1 row(s) in 0.74s

赞(0）回复(0）举报 2021-05-31

我来回答

在impala db中创建表作为select percentage子查询

2条答案

相关问题

热门标签

最新问答