Create table as select percentage subquery in Impala DB

user_8230537 0
Viewed 574

I'm newbie of Impala, i need to create table with select resultset, also, this sql is run in Java using JDBC, see my below query:

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);

I need to create table my_temp_table and insert data into this table, this is one SQL that i need to run. But it runs failed and gives errer as below:

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

After checking, i know Impala doesn't support SELECT clause subquery, we can only use subquery in FROM or WHERE clause, see Impala docs: https://impala.apache.org/docs/build/html/topics/impala_subqueries.html.

So for this question how can i do to solve this problem.

My thought:

  1. update sql to let it execute, I tried WITH like below sql, it works but can't be used in 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
  1. or, is there any statement like BEGIN ... END in MySQL or Oracle, then i can run this sql separately.
impala hadoop count sql java
2 Answers

With your examples, I would try these approaches that, I believe, could work fine. I checked the solution with 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

First example

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

Second example

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
user_85714980

I think a conditional average can do what you want simply and efficiently, with a single table scan:

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

You can turn this to a create table statement:

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
user_106767160
Related