shell - 如何添加分号；在每个使用 shell 脚本创建 ddl 语句之后

Question

我试图在每个 create view Hive ddl 语句后添加一个分号 (;)。我有一个文件，其中包含以下 ddl 语句:

CREATE VIEW `db1.table1` AS SELECT * FROM db2.table1
CREATE VIEW `db1.table2` AS SELECT * FROM db2.table2
CREATE VIEW `db1.table3` AS SELECT * FROM db3.table3
CREATE EXTERNAL TABLE `db1.table4`(
  `cus_id` int,
  `ren_mt` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
TBLPROPERTIES (
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1558705259')
CREATE EXTERNAL TABLE `sndbx_cmcx.effective_month1`(
  `customeridentifier` bigint, 
  `renewalmonth` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'transient_lastDdlTime'='1558713596')

我希望它看起来像下面这样。在每个创建 View 语句之后都有一个;在每个创建表之后都有一个;..

CREATE VIEW `db1.table1` AS SELECT * FROM db2.table1;
CREATE VIEW `db1.table2` AS SELECT * FROM db2.table2;
CREATE VIEW `db1.table3` AS SELECT * FROM db3.table3;
CREATE EXTERNAL TABLE `db1.table4`(
  `cus_id` int,
  `ren_mt` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
TBLPROPERTIES (
  'skip.header.line.count'='1', 
  'transient_lastDdlTime'='1558705259');
CREATE EXTERNAL TABLE `sndbx_cmcx.effective_month1`(
  `customeridentifier` bigint, 
  `renewalmonth` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'transient_lastDdlTime'='1558713596');

这是我使用的 shell 脚本:

#Change database before you run the script
 hiveDBName=$1;


 showcreate="show create table "
 terminate=";"
 tables=`hive -e "use $hiveDBName;show tables;"`
 tab_list=`echo "${tables}"`

  for list in $tab_list
  do
         echo "Generating table script for " #${hiveDBName}.${list}
          showcreatetable=${showcreatetable}${showcreate}${hiveDBName}.${list}${terminate}
        done

        echo " ====== Create Tables ======= : "# $showcreatetable

 #Creates a filter ddls
 hive -e "use $hiveDBName; ${showcreatetable}"> a.sql
 #Removes the Warn: from the file
 grep -v "WARN" a.sql > /home/path/my_ddls/${hiveDBName}_extract_all_tables.sql

 echo "Removing Filter File"
 #Remove Filter file
 rm -f a.sql

#Puts a ; after each create view statement in the document 
sed -i '/transient/s/$/;/' "/home/path/my_ddls/${hiveDBName}_extract_all_tables.sql"

这会生成 ddls 但它只会放置一个 ;在 create table 语句之后，但它不会在每个 create view 语句之后放置它。

有什么想法或建议吗？

Answer 1

我会采取简单的方法并利用 ; 不必与语句(的末尾)在同一行的可能性，并且可能有一个空洞的陈述。这给出:

sed -i -e '/^CREATE/i;' -e '$a;' "/home/path/my_ddls/${hiveDBName}_extract_all_tables.sql"