代码之家  ›  专栏  ›  技术社区  ›  DeepNightTwo

如何在配置单元insert partition语句中使用UDF值或列值,而不是常量值

hive-partitions hive
  •  0
  • DeepNightTwo  · 技术社区  · 7 年前 

    CREATE EXTERNAL TABLE `DailyData`(
  `entity_id` string,
  `payload` string)
PARTITIONED BY 
  (`date_of_data` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\u0010'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.SequenceFileInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat'
LOCATION
  '/home/data/dailydata'

    我每天都要运行一个作业,将数据插入到 daily_table

    它可以使用以下语句工作 

    INSERT INTO TABLE DailyData partition(date_of_data="20181126") 
SELECT id as entity_id, simpledata as payload from log_data;

    我的期望是它可以自动使用当前日期,而不是使用硬编码日期字符串(如20181126”)

    我试过了 

    INSERT INTO TABLE DailyData partition(date_of_data=from_unixtime(unix_timestamp(),'yyyyMMdd')) 
SELECT id as entity_id, simpledata as payload from log_data;

    得到了以下异常 

        NoViableAltException(26@[244:1: constant : ( Number | dateLiteral | timestampLiteral | intervalLiteral | StringLiteral | stringLiteralSequence | BigintLiteral | SmallintLiteral | TinyintLiteral | DecimalLiteral | charSetStringLiteral | booleanValue );])
            at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
            at org.antlr.runtime.DFA.predict(DFA.java:116)
            at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.constant(HiveParser_IdentifiersParser.java:4928)
            at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.partitionVal(HiveParser_IdentifiersParser.java:10726)
            at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.partitionSpec(HiveParser_IdentifiersParser.java:10560)
            at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.tableOrPartition(HiveParser_IdentifiersParser.java:10438)
            at org.apache.hadoop.hive.ql.parse.HiveParser.tableOrPartition(HiveParser.java:49929)
            at org.apache.hadoop.hive.ql.parse.HiveParser.insertClause(HiveParser.java:46629)
            at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:43233)
            at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:42451)
            at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:42321)
            at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1681)
            at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1152)
            at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:211)
            at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:171)
            at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:447)
            at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:330)
            at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1233)
            at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1274)
            at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1170)
            at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1160)
            at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:217)
            at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:169)
            at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:380)
            at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:740)
            at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:685)
            at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
            at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
            at java.lang.reflect.Method.invoke(Method.java:498)
            at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
            at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
    FAILED: ParseException line 1:65 cannot recognize input near 'from_unixtime' '(' 'unix_timestamp' in constant

    我试过了 

    INSERT INTO TABLE DailyData partition(date_of_data=data_of_date) 
SELECT id as entity_id, simpledata as payload, from_unixtime(unix_timestamp(),'yyyyMMdd') as data_of_date from log_data;

    得到了类似的例外。 

    NoViableAltException(26@[244:1: constant : ( Number | dateLiteral | timestampLiteral | intervalLiteral | StringLiteral | stringLiteralSequence | BigintLiteral | SmallintLiteral | TinyintLiteral | DecimalLiteral | charSetStringLiteral | booleanValue );])
        at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
        at org.antlr.runtime.DFA.predict(DFA.java:116)
        at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.constant(HiveParser_IdentifiersParser.java:4928)
        at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.partitionVal(HiveParser_IdentifiersParser.java:10726)
        at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.partitionSpec(HiveParser_IdentifiersParser.java:10560)
        at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.tableOrPartition(HiveParser_IdentifiersParser.java:10438)
        at org.apache.hadoop.hive.ql.parse.HiveParser.tableOrPartition(HiveParser.java:49929)
        at org.apache.hadoop.hive.ql.parse.HiveParser.insertClause(HiveParser.java:46629)
        at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:43233)
        at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:42451)
        at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:42321)
        at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1681)
        at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1152)
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:211)
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:171)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:447)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:330)
        at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1233)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1274)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1170)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1160)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:217)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:169)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:380)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:740)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:685)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
FAILED: ParseException line 1:51 cannot recognize input near 'date_of_data' ')' 'SELECT' in constant

    在分区语句中使用值而不是常量是否可行?

    1 回复  |  直到 7 年前
        1
  •  2
  •   shanmuga    7 年前

    这可以通过设置 hive.exec.dynamic.partition.mode=nonstrict;
    请注意中的最后一列 select 条款是 date_of_data 并指明 partition(date_of_data) 此列用作分区值。
    警告 如果分区中有多个值,记录将被发送到相应的分区。小心使用。 

    set hive.exec.dynamic.partition.mode=nonstrict;
INSERT INTO TABLE DailyData partition(date_of_data) 
SELECT id as entity_id, simpledata as payload, from_unixtime(unix_timestamp(),'yyyyMMdd') as date_of_data from log_data;

    https://cwiki.apache.org/confluence/display/hive/languagemanual+dml#LanguageManualDML-DynamicPartitionInserts

    推荐文章
    Victor Wong  ·  如何迭代列A以有条件地对列B求和?
    1 年前
    StripyTiger  ·  从配置单元中的列表检索元素
    2 年前
    mfcss  ·  SQL:使用年/月/日分区查询某个时间段的数据
    2 年前
    MOMO  ·  配置单元重命名具有分区的表中的列
    2 年前
    Minnu  ·  json结构类型加载到配置单元
    2 年前
    PPSATO  ·  替代在AWS Athena中使用last_value和first_value
    2 年前
    te time  ·  按组获取完整的行集
    2 年前
    MattRgx  ·  配置单元中的Regexp将一列拆分为3列
    4 年前
    mishalhaneef  ·  如何更新蜂箱颤振数据
    4 年前
    Bhanuchander Udhayakumar  ·  为什么spark sql的cpu利用率高于hive?
    8 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号