I have a Hive table like below..I would like have the first column to be a constant value (Its not coming in the XML file). if I hardcode the value, its not being displayed when I do a select on the table... what am I doing wrong? I tried using the concatenate operator also (with dummy xpath and adding the string)...still no luck ...any help is appreciated..
`
CREATE EXTERNAL TABLE IF NOT EXISTS raw_RMS_20170629(
SourceSystem STRING,
Account STRING,
Action STRING,
AllocationType STRING,
AlternativeTradeId STRING,
...
...
ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
WITH SERDEPROPERTIES (
"column.xpath.SourceSystem"="RMS",
"column.xpath.Account"="Trade/Account/text()",
"column.xpath.Action"="Trade/Action/text()",
"column.xpath.AllocationType"="Trade/AllocationType/text()",
...
...
Sample Data below. The external table is created dynamically everytime oozie workflow runs and I would like to add a constant field (sourcesystem = RMS or.. GSS etc when I create the table)
<queryReply>
<Trade xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:rms="http://www.example.org/store/schema/abc/durables/rms"
xmlns:rms-raw="http://www.example.org/td/tm/lite/rms-raw/v1.0">
<Account>AKA</Account>
<Action xsi:nil="true"/>
<AllocationType xsi:nil="true"/>
<TradeId>
<rms:SYSTEMID>97926437</rms:SYSTEMID>
</TradeId>
<TradeVersion>
<rms:VERSION>1</rms:VERSION>
</TradeVersion>
<InstrumentId xsi:nil="true"/>
<TraderSourceSystemName xsi:nil="true"/>
<TraderId xsi:nil="true"/>
<TraderName xsi:nil="true"/>
<TraderEmail xsi:nil="true"/>
<TradeStatus>Open</TradeStatus>
...
...
<Currency/>
<UnderlyingCurrency>
<UNDERCURRENCY>USD</UNDERCURRENCY>
</UnderlyingCurrency>
<ExpiryDate xsi:nil="true"/>
<FuturesExpiryDate xsi:nil="true"/>
<FutureSubType xsi:nil="true"/>
<CashBalance xsi:nil="true"/>
<FutureCode xsi:nil="true"/>
<UnderlyingExchange xsi:nil="true"/>
<CorrelationId xsi:nil="true"/>
</Trade>
</queryReply>
