在 hive 中提取多个 create table 语句 [英] Extract multiple create table statement in hive

问题描述

我需要从下面的代码中提取创建表语句.

I need to extract create table statments from below code.

    #!/bin/bash
rm -f tableNames.txt
rm -f HiveTableDDL.txt
beeline --showHeader=false --outputformat=tsv2 -u jdbc:hive2:// -n hive -e "show tables like 'test*';" > tableNames.txt 
wait
while read LINE
do
   beeline --showHeader=false --outputformat=tsv2 -u jdbc:hive2:// -n hive -e "show create table $LINE" | perl -ne 'BEGIN{$x=qx(cat test.txt);$x=~s/(.+)(create table.+?)(ROW FORMAT SERDE|STORED AS INPUTFORMAT|ROW FORMAT SERDE|OUTPUTFORMAT|LOCATION|TBLPROPERTIES)(.*)/$2/osm; print "$x STORED AS ORC\n" ; exit } '
   printf  ";\n\n" 
done < tableNames.txt >> HiveTableDDL.txt
rm -f tableNames.txt
echo "Table DDL generated"

我想要像下面这样的东西

I wanted to have something like below

CREATE TABLE `test`(
  `id` string COMMENT '',
  `age` string COMMENT '',
  `city` string COMMENT '') stored as orc;

CREATE TABLE `test_2`(
  `id` string COMMENT '',
  `age` string COMMENT '',
  `city` string COMMENT '') stored as orc;

推荐答案

检查这是否适合您.

> cat hive_table.txt2
show create table hive_table:
create table hive_table(id number,age number)
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
STORED AS INPUTFORMAT  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
LOCATION  'hdfs:/path/'
TBLPROPERTIES (   'spark.sql.sources ....)
show create table hive_table2:
create table hive_table2(id number,age number)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION  'hdfs:/path/'
TBLPROPERTIES (   'spark.sql.sources ....)
show create table hive_table3:
create table hive_table3(id number,age number)
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION  'hdfs:/path/'
TBLPROPERTIES (   'spark.sql.sources ....)
>
> hive_table.pl hive_table.txt2
create table hive_table(id number,age number)
 stored as orc
create table hive_table2(id number,age number)
 stored as orc
create table hive_table3(id number,age number)
 stored as orc
>

脚本是

> cat hive_table.pl
#!/usr/bin/perl
$file=$ARGV[0];
$x=qx(cat $file);
while($x=~m/(.+?)(create table.+?)(CREATE TABLE.+?)(PARTITIONED BY|STRED AS INPUTFORMAT|ROW FORMAT SERDE|OUTPUTFORMAT|LOCATION|TBLPROPERTIES)(.*)/iosm)
{
$x=$5;
$table_desc=$3;
print "$table_desc stored as orc\n";
}

>

编辑 1:

> cat hive_table.pl
#!/usr/bin/perl
$file=$ARGV[0];
$x=qx(cat $file);
while($x=~m/(.+?)(create table.+?)(PARTITIONED BY|STRED AS INPUTFORMAT|ROW FORMAT SERDE|OUTPUTFORMAT|LOCATION|TBLPROPERTIES)(.*)/iosm)
{
$x=$4;
$table_desc=$2;
print "$table_desc stored as orc\n";
}

>

> cat hive_table.txt3
create table hive_table(id number,age number)
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
STORED AS INPUTFORMAT  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
LOCATION  'hdfs:/path/'
TBLPROPERTIES (   'spark.sql.sources ....)
create table hive_table2(id number,age number)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION  'hdfs:/path/'
TBLPROPERTIES (   'spark.sql.sources ....)
create table hive_table3(id number,age number)
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION  'hdfs:/path/'
TBLPROPERTIES (   'spark.sql.sources ..)
>  hive_table.pl hive_table.txt3
create table hive_table(id number,age number)
 stored as orc
create table hive_table2(id number,age number)
 stored as orc
create table hive_table3(id number,age number)
 stored as orc
/etl/stage3/CAM/AN06599/work_2018/stack> cat hive_table.pl
#!/usr/bin/perl
$file=$ARGV[0];
$x=qx(cat $file);
$x="dummy".$x."dummy";
while($x=~m/(.+?)(create table.+?)(PARTITIONED BY|STRED AS INPUTFORMAT|ROW FORMAT SERDE|OUTPUTFORMAT|LOCATION|TBLPROPERTIES)(.*)/iosm)
{
$x=$4;
$table_desc=$2;
print "$table_desc stored as orc\n";
}

>

这篇关于在 hive 中提取多个 create table 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！