I am trying to replace a sting from a query using sed.It won't replace the variable with value Using the below code

SQL="CREATE EXTERNAL TABLE <table name>( user_id string, file_name string, file_type string, count string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( 'escapeChar'='\\', 'quoteChar'='\"', 'separatorChar'=',') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://<bucket name>/<folder name>/$client_id/'";
FINALSQL=$(echo $SQL | sed -e "s/\$client_id/$client_id/g") 

If I am print FINALSQL .It return the below string

CREATE EXTERNAL TABLE <table name>( user_id string, file_name string, file_type string, count string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( 'escapeChar'='\', 'quoteChar'='"', 'separatorChar'=',') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://<bucket name>/<folder name>//'

Note:-This is an Athena Query using cli command

Did I miss anything in the replacement logic? please correct if am wrong

CodePudding user response：

The problem is with the assignment of your variable SQL. If you would do a echo $SQL, you would see that the variable already does not contain a substring $client_id, so there is no surprise that nothing gets substituted.

If you set it as

SQL="CREATE EXTERNAL TABLE <table name>( user_id string, file_name string, file_type string, count string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( 'escapeChar'='\\', 'quoteChar'='\"', 'separatorChar'=',') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://<bucket name>/<folder name>/\$client_id/'"

your substitution code works for me.

Another, minor, point is that the way you use echo would squeeze any run of spaces into a single space. Not a problem with your current SQL string, but may bite you later.

If you insist in using sed for this task, you could either quote the argument to echo, or don't use th unnecessary pipeline. I would simply do a

FINALSQL=$(sed -e "s/\$client_id/$client_id/g" <<<$SQL)

to accomplish the task. Note that in any case, your sed approach will work only as long as client_id does not hold regexp characters, which might ruin the substitution.

CodePudding user response：

in SQL escape $client_id like so \$client_id

or if possible, use something like %%client_id%% throughout

refer

If the current character is , single-quote, or double-quote and it is not quoted, it shall affect quoting for subsequent characters up to the end of the quoted text.

(source)

Only the outer quotes are not quoted, the rule applies to them. The inner quotes are quoted, they do not affect subsequent characters