question Re: impala table creation with select command in Support Questions

impala table creation with select command

saisvk — Fri, 16 Sep 2022 11:57:26 GMT

When I tried to create a table in Impala it is showing the below error, I'm new to Hadoop so kindly help me out.

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table ordercrime2012
                              > LIKE
                              > LOCATION '/user/cloudera/svk'
                              > STORED AS ctas_file_format
                              > SELECT loc,crimetyp,count(crimetyp) from crime2012 GROUP BY loc,crimetyp  ORDER BY count(crimetyp) DESC LIMIT 6763;
Query: create table ordercrime2012 LIKE LOCATION '/user/cloudera/svk' STORED AS ctas_file_format SELECT loc,crimetyp,count(crimetyp) from crime2012 GROUP BY loc,crimetyp  ORDER BY count(crimetyp) DESC LIMIT 6763
ERROR: AnalysisException: Syntax error at:
create table ordercrime2012 LIKE LOCATION '/user/cloudera/svk' STORED AS ctas_file_format SELECT loc,crimetyp,count(crimetyp) from crime2012 GROUP BY loc,crimetyp  ORDER BY count(crimetyp) DESC LIMIT 6763
                                 ^
Encountered: LOCATION
Expected: IDENTIFIER

CAUSED BY: Exception: Syntax error

if anyone knows how to create the table for select command in Impala kindly let me know, please.

Thanks in advance.

Re: impala table creation with select command

csguna — Thu, 20 Jul 2017 05:41:36 GMT

@saisvk

Usually when you CREATE TABLE LIKE syntax you dont specify coloumn name , it will be dervied from the source table.

It just the precidence well try this you should be able to overcome this error

Note - Make sure you have the write / read access to the path you mention in the LOCATION .

Syntax

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE { [db_name.]table_name | PARQUET 'hdfs_path_of_parquet_file' }
  [COMMENT 'table_comment']
  [STORED AS file_format]
  [LOCATION 'hdfs_path']

create table t2 like t1 stored as textfile  LOCATION '/user/hduser';

Le me know if that works

Re: impala table creation with select command

saisvk — Fri, 21 Jul 2017 10:14:06 GMT

Thanks for your acknowledgement, it is working when I create new table, but problem is, in hive, we will create a table from select command like this, but I'm getting errors while I am creating a table based on select command in Impala, could you please tell me how to create a table in Impala using select command.

 HIve:

create table emp as 
select ename,esal from empdept;


Impala: 

???

Re: impala table creation with select command

csguna — Fri, 21 Jul 2017 10:24:24 GMT

@saisvk The syntax that I shared should work with impala and hive . which ever you like AS or LIKE you can use . if you got any error by using them in impala let me know .

Re: impala table creation with select command

AcharkiMed — Fri, 21 Jul 2017 10:26:22 GMT

Hi

See the CREATE TABLE AS SELECT paragraph here in documentation:

https://www.cloudera.com/documentation/enterprise/latest/topics/impala_create_table.html

Good luck.

Re: impala table creation with select command

saisvk — Fri, 21 Jul 2017 10:37:34 GMT

I dint get any error, but while fetching records it is giving result as 0 rows

Returned 0 row(s) in 1.00s
[localhost.localdomain:21000] > create table percentage112012 like percentagecrime2012 Stored as RCFILE Location '/user/hive/warehouse/dissertation.db/percentagecrime2012 ';
Query: create table percentage112012 like percentagecrime2012 Stored as RCFILE Location '/user/hive/warehouse/dissertation.db/percentagecrime2012 '
[localhost.localdomain:21000] > select * from percentage112012;
Query: select * from percentage112012
Query finished, fetching results ...

Returned 0 row(s) in 4.37s
[localhost.localdomain:21000] >

Re: impala table creation with select command

saisvk — Fri, 21 Jul 2017 10:51:12 GMT

Im not understanding how to create table for select command. Below i am leaving code kindly let me know according to that.

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > Create table A LIKE percentagecrime2012
                              > STORED AS RCFILE
                              > LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012'
                              > AS select _c1 from percentagecrime2012;
Query: create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' AS select _c1 from percentagecrime2012
ERROR: AnalysisException: Syntax error at:
create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' AS select _c1 from percentagecrime2012
                                                                                                                             ^
Encountered: AS
Expected

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' AS select _c1 from percentagecrime2012
                              > ;
Query: create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' AS select _c1 from percentagecrime2012
ERROR: AnalysisException: Syntax error at:
create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' AS select _c1 from percentagecrime2012
                                                                                                                             ^
Encountered: AS
Expected

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' LIKE select _c1 from percentagecrime2012;
Query: create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' LIKE select _c1 from percentagecrime2012
ERROR: AnalysisException: Syntax error at:
create table A LIKE percentagecrime2012 STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/percentagecrime2012' LIKE select _c1 from percentagecrime2012
                                                                                                                             ^
Encountered: LIKE
Expected

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] >

Re: impala table creation with select command

csguna — Fri, 21 Jul 2017 16:47:37 GMT

@saisvk

just type the below in the impala terminal mate

Create table A  LIKE percentagecrime2012  STORED AS RCFILE LOCATION '/user/hive/warehouse/dissertation.db/'

Note  - When you use LIKE

" you do not specify the columns at all; 
the column names and types are derived from the source table"

" Also it will only create empty table with same structure 
as your orginal table with NO DATA " 
hence when you perform select * from table_name it did not return rows. 

LOCATION - is the path of the location that you want your new created table to be .

if you want to clone the structure along with the data and your custom location then use below query

CREATE TABLE A STORED AS TEXTFILE LOCATION '/user/hive/warehouse/dissertation.db/' AS SELECT * FROM percentagecrime2012;

if you need more information let me know .

Re: impala table creation with select command

saisvk — Sat, 22 Jul 2017 10:14:15 GMT

@csguna

Sir, i have tried as you suggested me then also its giving below errors,

[localhost.localdomain:21000] > CREATE TABLE maxminonly2013 STORED AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013;
Query: create TABLE maxminonly2013 STORED AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
ERROR: AnalysisException: Syntax error at:
create TABLE maxminonly2013 STORED AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
                            ^
Encountered: STORED
Expected: LIKE

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > CREATE TABLE maxminonly2013 LIKE  AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013;
Query: create TABLE maxminonly2013 LIKE  AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
ERROR: AnalysisException: Syntax error at:
create TABLE maxminonly2013 LIKE  AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
                                  ^
Encountered: AS
Expected: IDENTIFIER

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > CREATE TABLE maxminonly2013 LIKE STORED AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013;
Query: create TABLE maxminonly2013 LIKE STORED AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
ERROR: AnalysisException: Syntax error at:
create TABLE maxminonly2013 LIKE STORED AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
                                 ^
Encountered: STORED
Expected: IDENTIFIER

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > CREATE TABLE maxminonly2013  STORED AS TEXTFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013;
Query: create TABLE maxminonly2013  STORED AS TEXTFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
ERROR: AnalysisException: Syntax error at:
create TABLE maxminonly2013  STORED AS TEXTFILE LOCATION '/user/hive/warehouse/d2013.db/' AS SELECT _c1,_c2  FROM maxmin2013
                             ^
Encountered: STORED
Expected: LIKE

CAUSED BY: Exception: Syntax error

Re: impala table creation with select command

csguna — Sat, 22 Jul 2017 11:19:05 GMT

@saisvk

Please read my previous response one more time . I told you that you dont have to mention column when you use LIKE and also it will create table with NO DATA hence you have to insert .

Now your query should be like this . copy paste it in your terminal .

The below will only CREATE - Does not LOAD DATA . ( LIKE )

CREATE TABLE maxminonly2013 LIKE maxmin2013 STORED AS RCFILE LOCATION '/user/hive/warehouse/d2013.db/'

The below will create and LOAD the data (AS)

CREATE TABLE maxminonly2013 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/dissertation.db/' AS SELECT * FROM maxmin2013 ;

please let me know if that helps .

Re: impala table creation with select command

saisvk — Sat, 22 Jul 2017 14:37:24 GMT

Thanks for your answer, your code is working when creating a new table using select * from tableA command, but when i use select column1,column2 from tableA is again giving error, below im attaching it,

[localhost.localdomain:21000] > refresh;
show tSuccessfully refreshed catalog
[localhost.localdomain:21000] > show tables;
Query: show tables
Query finished, fetching results ...
+------+
| name |
+------+
| emp  |
+------+
Returned 1 row(s) in 0.16s
[localhost.localdomain:21000] > select * from emp;;
Query: select * from emp
Query finished, fetching results ...
+--------+------------+-----------+------+-----------+----------+
| name   | dept       | job       | sal  | dob       | loc      |
+--------+------------+-----------+------+-----------+----------+
|        |            |           | NULL |           |          |
| ADAMS  | RESEARCH   | CLERK     | 7876 | 23-MAY-87 | DALLAS   |
| ALLEN  | SALES      | SALESMAN  | 7499 | 20-FEB-81 | CHICAGO  |
| BLAKE  | SALES      | MANAGER   | 7698 | 01-MAY-81 | CHICAGO  |
| CLARK  | ACCOUNTING | MANAGER   | 7782 | 09-JUN-81 | NEW YORK |
| FORD   | RESEARCH   | ANALYST   | 7902 | 03-DEC-81 | DALLAS   |
| JAMES  | SALES      | CLERK     | 7900 | 03-DEC-81 | CHICAGO  |
| JONES  | RESEARCH   | MANAGER   | 7566 | 02-APR-81 | DALLAS   |
| KING   | ACCOUNTING | PRESIDENT | 7839 | 17-NOV-81 | NEW YORK |
| MARTIN | SALES      | SALESMAN  | 7654 | 28-SEP-81 | CHICAGO  |
| MILLEA | ACCOUNTING | CLERK     | 7934 | 23-JAN-82 | NEW YORK |
| SCOTT  | RESEARCH   | ANALYST   | 7788 | 19-APR-87 | DALLAS   |
| SMITH  | RESEARCH   | CLERK     | 7369 | 17-DEC-80 | DALLAS   |
| TURNER | SALES      | SALESMAN  | 7844 | 08-SEP-81 | CHICAGO  |
| WARD   | SALES      | SALESMAN  | 7521 | 22-FEB-81 | CHICAGO  |
+--------+------------+-----------+------+-----------+----------+
Returned 15 row(s) in 2.00s
[localhost.localdomain:21000] > select name,job,loc from emp;
Query: select name,job,loc from emp
Query finished, fetching results ...
+--------+-----------+----------+
| name   | job       | loc      |
+--------+-----------+----------+
|        |           |          |
| ADAMS  | CLERK     | DALLAS   |
| ALLEN  | SALESMAN  | CHICAGO  |
| BLAKE  | MANAGER   | CHICAGO  |
| CLARK  | MANAGER   | NEW YORK |
| FORD   | ANALYST   | DALLAS   |
| JAMES  | CLERK     | CHICAGO  |
| JONES  | MANAGER   | DALLAS   |
| KING   | PRESIDENT | NEW YORK |
| MARTIN | SALESMAN  | CHICAGO  |
| MILLEA | CLERK     | NEW YORK |
| SCOTT  | ANALYST   | DALLAS   |
| SMITH  | CLERK     | DALLAS   |
| TURNER | SALESMAN  | CHICAGO  |
| WARD   | SALESMAN  | CHICAGO  |
+--------+-----------+----------+
Returned 15 row(s) in 1.17s
[localhost.localdomain:21000] > CREATE TABLE emp1 LIKE emp STORED AS TEXTFILE LOCATION '/user/hive/warehouse/svk.db/';
Query: create TABLE emp1 LIKE emp STORED AS TEXTFILE LOCATION '/user/hive/warehouse/svk.db/'
[localhost.localdomain:21000] > CREATE TABLE maxminonly2013 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/dissertation.db/' AS SELECT * FROM maxmin2013 ;
Query: create TABLE maxminonly2013 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/dissertation.db/' AS SELECT * FROM maxmin2013
ERROR: AnalysisException: Syntax error at:
create TABLE maxminonly2013 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/dissertation.db/' AS SELECT * FROM maxmin2013
                            ^
Encountered: STORED
Expected: LIKE

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > CREATE TABLE emp1 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/svk.db/' AS SELECT name,job,loc FROM emp;
Query: create TABLE emp1 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/svk.db/' AS SELECT name,job,loc FROM emp
ERROR: AnalysisException: Syntax error at:
create TABLE emp1 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/svk.db/' AS SELECT name,job,loc FROM emp
                  ^
Encountered: STORED
Expected: LIKE

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] >

LIKE ABOVE TABLE, I HAVE ONE CRIME2013 TABLE, FROM THE PARENT TABLE I HAVE SELECTED

SELECT crime,loc FROM CRIME2013; IT IS GIVING THE RESULT, NOW I WANT TO CREATE A TABLE BASE ON SELECT crime,loc FROM CRIME2013;

CREATE TABLE crimeloc LIKE CRIME2013 STORED AS RCFILE LOCATION '/user/hive/warehouse/svk.db';

now above it is creating a new table called crimeloc with 0 rows,

NOW I want to create a table and insert the SELECT crime,loc FROM CRIME2013; result into above created new table crimeloc.

Now if I try:

CREATE TABLE crimeloc STORED AS RCFILE LOCATION '/user/hive/warehouse/svk.db' AS SELECT crime,loc from CRIME2013;

Then it is giving below errors,

create TABLE emp1 STORED AS TEXTFILE LOCATION '/user/hive/warehouse/svk.db/' AS SELECT name,job,loc FROM emp
                  ^
Encountered: STORED
Expected: LIKE

Re: impala table creation with select command

csguna — Sun, 23 Jul 2017 01:52:45 GMT

@saisvk mate

please look into my previous response i have highlighted what will happen when you use LIKE

CREATE TABLE crimeloc LIKE CRIME2013 STORED AS RCFILE LOCATION '/user/hive/warehouse/svk.db';
now above it is creating a new table called crimeloc with 0 rows,

Re: impala table creation with select command

csguna — Sun, 23 Jul 2017 02:33:50 GMT

try this

create TABLE emp1  AS SELECT name,job,loc FROM emp;

this should create you a table called emp under your /user/hive/warehouse/your_database_folder

perform a select in emp1 - you should get results.

let me know if that works

Re: impala table creation with select command

saisvk — Thu, 27 Jul 2017 23:52:23 GMT

Hello, create table emp1 as select name, dept from emp; this syntax is working in the hive, but when I try the same syntax in Impala it's giving below error

[localhost.localdomain:21000] > use svk;
Query: use svk
[localhost.localdomain:21000] > show tables;
Query: show tables
Query finished, fetching results ...
+------+
| name |
+------+
| emp  |
| emp1 |
+------+
Returned 2 row(s) in 0.53s
[localhost.localdomain:21000] > select * from emp;
Query: select * from emp
Query finished, fetching results ...
+--------+------------+-----------+------+-----------+----------+
| name   | dept       | job       | sal  | dob       | loc      |
+--------+------------+-----------+------+-----------+----------+
|        |            |           | NULL |           |          |
| ADAMS  | RESEARCH   | CLERK     | 7876 | 23-MAY-87 | DALLAS   |
| ALLEN  | SALES      | SALESMAN  | 7499 | 20-FEB-81 | CHICAGO  |
| BLAKE  | SALES      | MANAGER   | 7698 | 01-MAY-81 | CHICAGO  |
| CLARK  | ACCOUNTING | MANAGER   | 7782 | 09-JUN-81 | NEW YORK |
| FORD   | RESEARCH   | ANALYST   | 7902 | 03-DEC-81 | DALLAS   |
| JAMES  | SALES      | CLERK     | 7900 | 03-DEC-81 | CHICAGO  |
| JONES  | RESEARCH   | MANAGER   | 7566 | 02-APR-81 | DALLAS   |
| KING   | ACCOUNTING | PRESIDENT | 7839 | 17-NOV-81 | NEW YORK |
| MARTIN | SALES      | SALESMAN  | 7654 | 28-SEP-81 | CHICAGO  |
| MILLEA | ACCOUNTING | CLERK     | 7934 | 23-JAN-82 | NEW YORK |
| SCOTT  | RESEARCH   | ANALYST   | 7788 | 19-APR-87 | DALLAS   |
| SMITH  | RESEARCH   | CLERK     | 7369 | 17-DEC-80 | DALLAS   |
| TURNER | SALES      | SALESMAN  | 7844 | 08-SEP-81 | CHICAGO  |
| WARD   | SALES      | SALESMAN  | 7521 | 22-FEB-81 | CHICAGO  |
+--------+------------+-----------+------+-----------+----------+
Returned 15 row(s) in 30.02s
[localhost.localdomain:21000] > select dept,sal,loc from emp;
Query: select dept,sal,loc from emp
Query finished, fetching results ...
+------------+------+----------+
| dept       | sal  | loc      |
+------------+------+----------+
|            | NULL |          |
| RESEARCH   | 7876 | DALLAS   |
| SALES      | 7499 | CHICAGO  |
| SALES      | 7698 | CHICAGO  |
| ACCOUNTING | 7782 | NEW YORK |
| RESEARCH   | 7902 | DALLAS   |
| SALES      | 7900 | CHICAGO  |
| RESEARCH   | 7566 | DALLAS   |
| ACCOUNTING | 7839 | NEW YORK |
| SALES      | 7654 | CHICAGO  |
| ACCOUNTING | 7934 | NEW YORK |
| RESEARCH   | 7788 | DALLAS   |
| RESEARCH   | 7369 | DALLAS   |
| SALES      | 7844 | CHICAGO  |
| SALES      | 7521 | CHICAGO  |
+------------+------+----------+
Returned 15 row(s) in 1.17s
[localhost.localdomain:21000] > create table emp2 as 
                              > select dept,sal,loc from emp;
Query: create table emp2 as select dept,sal,loc from emp
ERROR: AnalysisException: Syntax error at:
create table emp2 as select dept,sal,loc from emp
                  ^
Encountered: AS
Expected: LIKE

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table emp2 LIKE select dept,sal,loc from emp;
Query: create table emp2 LIKE select dept,sal,loc from emp
ERROR: AnalysisException: Syntax error at:
create table emp2 LIKE select dept,sal,loc from emp
                       ^
Encountered: SELECT
Expected: IDENTIFIER

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table emp2 as select dept,sal,loc from svk.emp;
Query: create table emp2 as select dept,sal,loc from svk.emp
ERROR: AnalysisException: Syntax error at:
create table emp2 as select dept,sal,loc from svk.emp
                  ^
Encountered: AS
Expected: LIKE

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] >

Re: impala table creation with select command

csguna — Fri, 28 Jul 2017 02:59:04 GMT

This is wrong

[localhost.localdomain:21000] > create table emp2 LIKE select dept,sal,loc from emp;
Query: create table emp2 LIKE select dept,sal,loc from emp
ERROR: AnalysisException: Syntax error at:
create table emp2 LIKE select dept,sal,loc from emp

This is correct syntax

create table emp2 LIKE emp;

Try this

create table emp2 AS  select dept,sal,loc from svk.emp;

The wired part is I am able to perform the same syntax with "as" as small refer the screen You have got some formatting issue check that your query does not have any unwanted space and mostly like just type it instead of copy and paste .

Re: impala table creation with select command

saisvk — Tue, 15 Aug 2017 01:46:53 GMT

Returned 15 row(s) in 0.39s
[localhost.localdomain:21000] > create table emp2 AS 
                              > select name,dept,job from svk.emp;
Query: create table emp2 AS select name,dept,job from svk.emp
ERROR: AnalysisException: Syntax error at:
create table emp2 AS select name,dept,job from svk.emp
                  ^
Encountered: AS
Expected: LIKE

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table emp2 LIKE select name,dept,job from svk.emp;
Query: create table emp2 LIKE select name,dept,job from svk.emp
ERROR: AnalysisException: Syntax error at:
create table emp2 LIKE select name,dept,job from svk.emp
                       ^
Encountered: SELECT
Expected: IDENTIFIER

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table emp2 LIKE emp;
Query: create table emp2 LIKE emp
[localhost.localdomain:21000] > create table emp2 AS select name,dept,job from svk.emp;
Query: create table emp2 AS select name,dept,job from svk.emp
ERROR: AnalysisException: Syntax error at:
create table emp2 AS select name,dept,job from svk.emp
                  ^
Encountered: AS
Expected: LIKE

CAUSED BY: Exception: Syntax error
[localhost.localdomain:21000] > create table emp3 as select name,dept from emp;
Query: create table emp3 as select name,dept from emp
ERROR: AnalysisException: Syntax error at:
create table emp3 as select name,dept from emp
                  ^
Encountered: AS
Expected: LIKE

i have followed as you said, but it is giving below errors