2

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.

Community
  • 1
  • 1
Hassan
  • 43
  • 3
  • 6

2 Answers2

2

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.

Community
  • 1
  • 1
Dennis Jaheruddin
  • 21,208
  • 8
  • 66
  • 122
1

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

Aditya
  • 2,385
  • 18
  • 25
  • @user3118213 Sorry ,problem with backticking character, please try with my updated answer now – Aditya Aug 12 '16 at 08:52
  • FAILED: ParseException line 1:70 mismatched input ',' expecting < near 'STRUCT' in struct type – Hassan Aug 12 '16 at 08:55
  • @user3118213 Just from reading that I would say that it does not complain about the `-` sign, but that it just expects some information about what the struct should look like. -- Does that error really disappear if you replace `-` with `_`? – Dennis Jaheruddin Aug 12 '16 at 08:57
  • agree with @Dennis Jaheruddin, @user3118213 - it woked fine for me in my environment after enclosing column names with ` , example - \`male-above-65\` , can you please post your complete query that you are trying after alteration – Aditya Aug 12 '16 at 09:02
  • back ticking character is working when ever you want to use special character or reserved words in column name, but the issue shows up if you use `X-User` for example as a column name inside an struct. below is an example: struct < `X-User` : string> – Hassan Aug 12 '16 at 09:18
  • @DennisJaheruddin it worked outside of STRCT but it doesnt work inside of STRUCT. please run this query in ur environment : 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';" – Hassan Aug 12 '16 at 09:21
  • 1
    @Hassan That appears to be a [known limitation](https://issues.apache.org/jira/browse/HIVE-13748), unfortunately. Consider replacing the - with a _. – Dennis Jaheruddin Aug 12 '16 at 10:53