0

There is a HIVE table with around 100 columns, partitioned by columns ClientNumber and Date. I am trying to insert data from another HIVE table into only 30 columns as well as create Date partitions dynamically.

The issue is that all data gets loaded into "ClientNumber=123/date=__HIVE_DEFAULT_PARTITION__" partition which is not quite expected.

The script looks like this:

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

FROM DBName.Table2
INSERT INTO TABLE DBName.Table1
PARTITION(ClientNumber=123, `Date`)   
(col1, col2, ..., col29, `Date`) 
SELECT 
col1, col2, ..., col29, eventDate as `Date`

Date values retrieved by the query are all correct, no NULL or other uncharacteristic values. Removing or adding Dynamic partitioning parameters makes no difference. Providing specific Date value (instead of dynamic query-populated value) results in creation of correct partitions.

Is there any trick I am missing or some issue with populating specific number of columns in partitioned tables?

P.S. Is listing all other columns (col31 - col100) in import script as NULL worth considering?

VasiliK
  • 1
  • 1
  • 1
  • Update: 1. `Date` column is stated as partitioning field during table creation. Reserved keyword or alias for column is not an issue — works in other scenarios as well. 2. Listing all remaining columns using 'cast(NULL as string) as colN' definition can be considered workaround for this case. However, the script becomes much larger and I would still like to understand the issue with INSERT of specific fields. – VasiliK Nov 21 '16 at 15:24

3 Answers3

0

If I get you question right, You should not have 'date' field in insert or select. If you are using aliase it may break requirments of partition rule. as it id expecting same field which is defined schema. the script should be ... 

FROM DBName.Table2
INSERT INTO TABLE DBName.Table1
PARTITION(ClientNumber=123, Date)   
SELECT col1, col2, ..., col29, eventDate as Date
Ravinder Karra
  • 307
  • 1
  • 3
  • 8
  • Thanks for the suggestion. However, the `Date` column is used to decide which dynamic partition should be used and is also calculated from some unix timestamp (which I simply marked as eventDate). So `Date` alias is necessary — and is working in other scenarios where I provide NULL values for remaining fields. – VasiliK Nov 21 '16 at 15:17
  • @vasilik, Updated Query can you try this one – Ravinder Karra Nov 21 '16 at 17:52
  • Running the suggested query returns: "SemanticException [Error 10044]: Cannot insert into target table because column number/types are different 'Date': Table insclause-0 has 100 columns, but query has 30 columns." I have to provide list of specific fields I want to insert, otherwise it is not expected to work. Inserting full list of fields works fine, but this does not solve the issue. – VasiliK Nov 22 '16 at 09:24
0

Yes, there is a better way to supply many column names. You can use Hive's "Regex Column Specification": regular expressions for column names. Your query would be:

    SET hive.support.quoted.identifiers=none;
    FROM DBName.Table2
    INSERT INTO TABLE DBName.Table1
    PARTITION(ClientNumber=123, Date)   
    SELECT `(eventDate)?+.+`, eventDate as Date;

This means "select all names except eventDate from Table2".

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-REGEXColumnSpecification

This is still clumsy for some use cases, and other places I've worked at wrote scripts to autogenerate Hive queries from table schemas.

Jack Parsons
  • 161
  • 7
0

In case it could be usefull for someone it is worth to read the answer in Corrupt rows written to __HIVE_DEFAULT_PARTITION__ when attempting to overwrite Hive partition

I have spent some silly hours with the same issue before I have realized that there is limitation that the partitioned column should be on the end of the DF. In my case I have simply changed the SQL query for my DF in a way that the partitioned column is selected as last.

mattino
  • 95
  • 7