Skip hyphen in hive

2
votes

I have executed a query in HIVE CLI that should generate an External Table . "create EXTERNAL TABLE IF NOT EXISTS hassan( code int, area_name string, male_60_64 STRUCT, male_above_65 STRUCT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';"

It works fine but if I put "-" instead of "_" I will face with error.

"create EXTERNAL TABLE IF NOT EXISTS hassan ( code int, area_name string, male-60-64 STRUCT< c1 : string, x-user : string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';"

Any help would be greatly appreciated.

2
hadoophivemapreducehiveqlbigdata
what is the error? – Taha Naqvi

2 Answers

2
votes

The answer by Addy already provided an example of how to use a hyphen in a column name. Here is an addition that describes how this works in different versions of Hive, according to the documentation:

  • In Hive 0.12 and earlier, only alphanumeric and underscore characters are allowed in table and column names.
  • In Hive 0.13 and later, column names can contain any Unicode character (see HIVE-6013). Any column name that is specified within backticks (`) is treated literally. Within a backtick string, use double backticks (``) to represent a backtick character. Backtick quotation also enables the use of reserved keywords for table and column identifiers.
  • To revert to pre-0.13.0 behavior and restrict column names to alphanumeric and underscore characters, set the configuration property hive.support.quoted.identifiers to none. In this configuration, backticked names are interpreted as regular expressions. For details, see Supporting Quoted Identifiers in Column Names.

In addition to that, you can also find the syntax for STRUCT there, which should help you with the error that you mentioned in the comments:

struct_type : STRUCT < col_name : data_type [COMMENT col_comment], ...>

Update:

Note that hyphens in complex types (so inside structs) do not appear to be supported.

1
votes

Try Quoted Identifiers

create table hassan( code int, `area_name` string, `male-60-64` STRUCT, `male-above-65` STRUCT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

Reference:

https://issues.apache.org/jira/secure/attachment/12618321/QuotedIdentifier.html