Support Questions
Find answers, ask questions, and share your expertise

HQL /hive my code to create a table in a database

New Contributor

my code to create a table in a database

CREATE TABLE IF NOT EXISTS sei_eclide_test_bi.tmp_ee_action
(
act_centre_nom VARCHAR(50),
act_snapshot_date TIMESTAMP,
act_import_date TIMESTAMP,
act_id VARCHAR(25),
act_type VARCHAR(50),
act_objetmaitre_id VARCHAR(25),
act_objetmaitre_role VARCHAR(120),
act_datecreation TIMESTAMP,
act_observations VARCHAR(2100)

)

PARTITIONED BY(CENTRE_CODE VARCHAR(50))

stored as orc
;

my code

Error: Error while compiling statement: FAILED: ParseException line 4:24 cannot recognize input near ',' 'act_id' 'VARCHAR' in column type (state=42000,code=40000) 0: jdbc:hive2://noeyy0zl.noe.edf.fr:2181,noey>

my code does not work in hive and I do not understand why

2 REPLIES 2

Super Guru

@othmani islem

Which version of Hive are you using?

Varchar type is only supported starting from Hive 0.12.0
i'm able to run your same create table statement without any issues.

hive> CREATE TABLE IF NOT EXISTS sei_eclide_test_bi.tmp_ee_action
(
act_centre_nom VARCHAR(50),
act_snapshot_date TIMESTAMP,
act_import_date TIMESTAMP,
act_id VARCHAR(25),
act_type VARCHAR(50),
act_objetmaitre_id VARCHAR(25),
act_objetmaitre_role VARCHAR(120),
act_datecreation TIMESTAMP,
act_observations VARCHAR(2100)
)
PARTITIONED BY(CENTRE_CODE VARCHAR(50))
stored as orc
;

Here is the describe on the table that i have created,

hive> desc tmp_ee_action;
+--------------------------+-----------------------+-----------------------+--+
|         col_name         |       data_type       |        comment        |
+--------------------------+-----------------------+-----------------------+--+
| act_centre_nom           | varchar(50)           |                       |
| act_snapshot_date        | timestamp             |                       |
| act_import_date          | timestamp             |                       |
| act_id                   | varchar(25)           |                       |
| act_type                 | varchar(50)           |                       |
| act_objetmaitre_id       | varchar(25)           |                       |
| act_objetmaitre_role     | varchar(120)          |                       |
| act_datecreation         | timestamp             |                       |
| act_observations         | varchar(2100)         |                       |
| centre_code              | varchar(50)           |                       |
|                          | NULL                  | NULL                  |
| # Partition Information  | NULL                  | NULL                  |
| # col_name               | data_type             | comment               |
|                          | NULL                  | NULL                  |
| centre_code              | varchar(50)           |                       |
+--------------------------+-----------------------+-----------------------+--+
If you are using Hive version < 0.12.0
Varchar datatype is not supported in Hive, Use String datatype

Try the below create table statement:

hive> CREATE TABLE IF NOT EXISTS sei_eclide_test_bi.tmp_ee_action
(act_centre_nom String,
act_snapshot_date TIMESTAMP,
act_import_date TIMESTAMP,
act_id String,
act_type String,
act_objetmaitre_id String,
act_objetmaitre_role String,
act_datecreation TIMESTAMP,
act_observations String)
PARTITIONED BY(CENTRE_CODE String)
stored as orc;

Super Guru

@othmani islem

If the answer addressed your question,Take a moment to Log in and Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues and close this thread.