Hive creating a table but getting FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns

Question

Here is the code I am using to create the table:

CREATE TABLE vi_vb(cTime STRING, VI STRING, Vital STRING, VB STRING)
PARTITIONED BY(cTime STRING, VI STRING)
CLUSTERED BY(VI) SORTED BY(cTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '1'
    COLLECTION ITEMS TERMINATED BY '2'
    MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

I don't really know what the problem is, does anyone have suggestions?

score 26 · Answer 1 · answered Mar 02 '15 at 11:36

26

Partition by columns should not be in create table definition. Partition by columns will be automatically added to table columns.

answered Mar 02 '15 at 11:36

Pravin Bange

399
5
16

2

Wasn't clear to me -solution simply remove cTime STRING, VI STRING from the create table – shimi_tap Nov 06 '17 at 11:40
4

And how will it know which column number it is? I have 3 columns C1,C2,C3, and I say create table T(Y1 int, Y3 int) partitioned by Y2; how will it know which one I want for Y2 (there are 3 possibilities)? This doesn't make any effing sense... – pavel_orekhov Dec 27 '18 at 13:43
1

You're supposed to use the Hive format in the path of your S3 files (e.g., //year=2022/month=01/day=12/yourfile.parquet). Then define the year, month and day as your partitions. Then run `MSCK REPAIR TABLE ` to add those 3 new partitions to the metastore. This partition names will be used to inspect your path and figure out how to physically data are organized so not all files need to be scanned when the WHERE uses any partition. More about this at https://docs.aws.amazon.com/athena/latest/ug/partitions.html. Hope this helps. – Nicolas Dao Jan 12 '22 at 08:04

score 9 · Answer 2 · answered Jul 15 '15 at 10:16

9

The columns "cTime, VI" are defined both as a normal column and as well as partitioning columns.

answered Jul 15 '15 at 10:16

Abhis

585
9
25

score 0 · Answer 3 · edited Sep 14 '20 at 10:26

0

CREATE TABLE vi_vb(Vital STRING, VB STRING)
PARTITIONED BY(cTime STRING, VI STRING)
CLUSTERED BY(VI) SORTED BY(cTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '1'
    COLLECTION ITEMS TERMINATED BY '2'
    MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

edited Sep 14 '20 at 10:26

4b0

21,981
30
95
142

answered Sep 14 '20 at 10:18

narasimha

11
1

5

It's good practice on StackOverflow to add an explanation as to why your solution should work. – 4b0 Sep 14 '20 at 10:27

Hive creating a table but getting FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns

3 Answers3