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

[#3]

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 

[#4]

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

[#5]

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 . 

[#6]

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

[#7]

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.