I have a shell script that automates the beeline utility to do below things:
Truncate the staging table Load the data to the staging table append the history table.
So far so good. My code is Truncating and loading the data to the stating table, but it not appending any data to the history table. Because when I fire the command "select count(*) from history_tbl". I get 0 records in beeline.
+------+--+
| _c0 |
+------+--+
| 0 |
+------+--+
Surprisingly, when I do a "select * from history_tbl;"
I get to see actual data
| "601809196400"|2018-09-19|"default"|"19"|2018-09-24 08:07:36|0.181156|0.514380|0.033541|0.171328|0.035591|0.064005|0|"df"|
"SYSTEM"|"A3"|"LT40"|"OTH"|"1_1day"|"TRADE"|"NA"|"Z_S"|"40-55"|"M"|"2"|0|"8_14"|0|"N"|"Unilateral
| left knee"|""|"SYSTEM"|142|0|0.400000|"UN"|1|"9111"|"6"|"S"|53|"M"|23700.000000|"N"|13|"M"||"rico
"|"zuba"|"BY "|"chris
"|"2023-07-30"|0|"PR" | NULL| NULL| NUL| NULL| NULL | NU
I am really confused with this characteristics.
Below is the shell script.
#!/bin/bash
timestamp=$(date +%Y-%m-%d-%H:%M:%S:%N)
number=$RANDOM
unix_time=$(date +%Y-%m-%d-%H:%M:%S)
cd path to script
export BEELINE_PREFIX='"jdbc:hive2://server:port,server:port,server:port/;serviceDiscoveryMode=zookeeper;zookeeperNamespace=hiveserver2;principal=hive/server@hello.COM"'
beeline -u $BEELINE_PREFIX -e "use next;"
echo "truncating the staging table tbl..."
beeline -u $BEELINE_PREFIX -e "TRUNCATE TABLE next.tbl;"
echo "Loading the data in staging table tbl"
beeline -u $BEELINE_PREFIX -e "LOAD DATA LOCAL INPATH '/path/tbl' INTO TABLE next.tbl;"
echo "Appending the data into history table HIST_tbl.."
beeline -u $BEELINE_PREFIX -e "insert into table next.HIST_tbl select *,'$unix_time','$number' from next.tbl;"
