CROSS APPLY SQL Server query on Hive
<p>HDP-2.5.0.0 using Ambari 2.4.0.1</p>

<p>The Hive table ReportSetting is as follows :</p>

<pre><code>id int

serializedreportsetting String
</code></pre>

<p>The column 'serializedreportsetting' is an XML data type in the source SQL Server db but is converted to String during Sqoop import, this is how it looks in SQL Server :</p>

<pre><code>&lt;ReportSettings4 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
  &lt;Trigger&gt;
  &lt;Manual&gt;true&lt;/Manual&gt;
  &lt;/Trigger&gt;
&lt;StartTime&gt;
    &lt;Year&gt;8&lt;/Year&gt;
    &lt;Month&gt;1&lt;/Month&gt;
    &lt;Day&gt;1&lt;/Day&gt;
    &lt;Hour&gt;0&lt;/Hour&gt;
    &lt;Minute&gt;0&lt;/Minute&gt;
  &lt;/StartTime&gt;
  &lt;ReportPeriod&gt;
    &lt;Month&gt;0&lt;/Month&gt;
    &lt;Day&gt;0&lt;/Day&gt;
    &lt;Hour&gt;0&lt;/Hour&gt;
    &lt;Minute&gt;5&lt;/Minute&gt;
  &lt;/ReportPeriod&gt;
  &lt;Theft&gt;
    &lt;DigitalInput&gt;true&lt;/DigitalInput&gt;
    &lt;Can&gt;false&lt;/Can&gt;
  &lt;/Theft&gt;
  &lt;SequenceNo&gt;0&lt;/SequenceNo&gt;
&lt;/ReportSettings4&gt;
</code></pre>

<p>In Hive table :</p>

<pre><code>&lt;ReportSettings4 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;&lt;Trigger&gt;&lt;Manual&gt;true&lt;/Manual&gt;&lt;/Trigger&gt;&lt;StartTime&gt;&lt;Year&gt;8&lt;/Year&gt;&lt;Month&gt;12&lt;/Month&gt;&lt;Day&gt;31&lt;/Day&gt;&lt;Hour&gt;23&lt;/Hour&gt;&lt;Minute&gt;34&lt;/Minute&gt;&lt;/StartTime&gt;&lt;ReportPeriod&gt;&lt;Month&gt;0&lt;/Month&gt;&lt;Day&gt;0&lt;/Day&gt;&lt;Hour&gt;4&lt;/Hour&gt;&lt;Minute&gt;0&lt;/Minute&gt;&lt;/ReportPeriod&gt;&lt;Theft&gt;&lt;DigitalInput&gt;false&lt;/DigitalInput&gt;&lt;Can&gt;false&lt;/Can&gt;&lt;/Theft&gt;&lt;SequenceNo&gt;3&lt;/SequenceNo&gt;&lt;/ReportSettings4&gt;
</code></pre>

<p>The query that works fine on the SQL Server :</p>

<pre><code>SELECT
r.VehicleId
,rs.value('(Trigger/Manual)[1]', 'bit') AS RS_Trigger_Manual, ,CAST(CONCAT(CASE WHEN rs.value('(StartTime/Year)[1]', 'int') &lt; 10 THEN CONCAT('200',rs.value('(StartTime/Year)[1]', 'int')) ELSE CONCAT('20',rs.value('(StartTime/Year)[1]', 'int')) END,'-',rs.value('(StartTime/Month)[1]', 'int'),'-',rs.value('(StartTime/Day)[1]', 'int'),' ',rs.value('(StartTime/Hour)[1]', 'int'),':',rs.value('(StartTime/Minute)[1]', 'int'),':','00.000') AS datetime) AS RS_StartTime
,rs.value('(ReportPeriod/Month)[1]', 'int') AS RS_ReportPeriod_Month
,rs.value('(ReportPeriod/Day)[1]', 'int') AS RS_ReportPeriod_Day
,rs.value('(ReportPeriod/Hour)[1]', 'int') AS RS_ReportPeriod_Hour
,rs.value('(ReportPeriod/Minute)[1]', 'int') AS RS_ReportPeriod_Minute
,rs.value('(Theft/DigitalInput)[1]', 'bit') AS RS_Theft_DigitalInput
,rs.value('(Theft/Can)[1]', 'bit') AS RS_Theft_Can,rs.value('(SequenceNo)[1]', 'int') 

AS RS_SequenceNo FROM ReportSetting r
  CROSS APPLY SerializedReportSetting.nodes('/*') AS ReportSettings(rs)
</code></pre>

<p>I could think of/do the following :</p>

<ol>
<li>To use the CROSS APPLY, I guess <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView#LanguageManualLateralView-Example" rel="nofollow noreferrer">lateral view</a> needs to be used, here I don't have the serializedreportsetting as an array, so explode() won't work. Can someone validate if I'm thinking in the right direction</li>
<li><p>I simply tried to get the data in serializedreportsetting as columns using the <a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+XPathUDF" rel="nofollow noreferrer">built-in xpath udf</a>, however, I don't get any records, few trials are as follows :</p>

<p>select xpath(SerializedReportSetting,'/*') from ReportSetting limit 1;</p>

<p>select xpath(SerializedReportSetting,'/ReportSettings4') from ReportSetting limit 1;</p>

<p>select xpath(SerializedReportSetting,'/Trigger/Manual') from ReportSetting limit 1;</p></li>
</ol>

<p>**********UPDATE-1**********</p>

<p>I used the regexp_replace to handle the above challenge :</p>

<pre><code>SELECT id,
  xpath_string(SerializedReportSetting,'/ReportSettings/Trigger/Manual')        AS RS_Trigger_Manual,
  xpath_string(SerializedReportSetting,'/ReportSettings/Trigger/DriveChange')   AS RS_Trigger_DriveChange
FROM
  (SELECT id,
    regexp_replace(SerializedReportSetting, 'ReportSettings+\\d','ReportSettings') AS SerializedReportSetting
  FROM reportsetting
  WHERE id IN (1701548,3185,1700231,1700232)
  ) reportsetting_regex;
</code></pre>