impala table creation with select command

[#1]

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.

[#2]

@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 

[#3]

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: 

???

[#4]

@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  . 

[#5]

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] > 

[#6]

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] > 

[#7]

@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 . 

[#8]

@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

[#9 · Accepted solution]

@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 . 

[#10]

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