Reply
Explorer
Posts: 21
Registered: ‎05-18-2017

impala table creation with select command

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.

Champion
Posts: 562
Registered: ‎05-16-2016

Re: impala table creation with select command

[ Edited ]

@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 

 

 

Explorer
Posts: 21
Registered: ‎05-18-2017

Re: impala table creation with select command

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: 

???

 

Champion
Posts: 562
Registered: ‎05-16-2016

Re: impala table creation with select command

[ Edited ]

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

Explorer
Posts: 29
Registered: ‎07-17-2017

Re: impala table creation with select command

[ Edited ]

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.

Explorer
Posts: 21
Registered: ‎05-18-2017

Re: impala table creation with select command

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] > 
Explorer
Posts: 21
Registered: ‎05-18-2017

Re: impala table creation with select command

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] > 
Champion
Posts: 562
Registered: ‎05-16-2016

Re: impala table creation with select command

[ Edited ]

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

Explorer
Posts: 21
Registered: ‎05-18-2017

Re: impala table creation with select command

@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
Highlighted
Champion
Posts: 562
Registered: ‎05-16-2016

Re: impala table creation with select command

[ Edited ]

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

Announcements