HDP-2.5.0.0 con Ambari 2.4.0.1
El ReportSetting de la tabla de Hive es el siguiente:
id int
serializedreportsetting String
La columna 'serializedreportsetting' es un tipo de datos XML en la base de datos de SQL Server de origen, pero se convierte a String durante la importación de Sqoop, así es como se ve en SQL Server:
<ReportSettings4 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Trigger>
<Manual>true</Manual>
</Trigger>
<StartTime>
<Year>8</Year>
<Month>1</Month>
<Day>1</Day>
<Hour>0</Hour>
<Minute>0</Minute>
</StartTime>
<ReportPeriod>
<Month>0</Month>
<Day>0</Day>
<Hour>0</Hour>
<Minute>5</Minute>
</ReportPeriod>
<Theft>
<DigitalInput>true</DigitalInput>
<Can>false</Can>
</Theft>
<SequenceNo>0</SequenceNo>
</ReportSettings4>
En la tabla Hive:
<ReportSettings4 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Trigger><Manual>true</Manual></Trigger><StartTime><Year>8</Year><Month>12</Month><Day>31</Day><Hour>23</Hour><Minute>34</Minute></StartTime><ReportPeriod><Month>0</Month><Day>0</Day><Hour>4</Hour><Minute>0</Minute></ReportPeriod><Theft><DigitalInput>false</DigitalInput><Can>false</Can></Theft><SequenceNo>3</SequenceNo></ReportSettings4>
La consulta que funciona bien en SQL Server:
SELECT
r.VehicleId
,rs.value('(Trigger/Manual)[1]', 'bit') AS RS_Trigger_Manual, ,CAST(CONCAT(CASE WHEN rs.value('(StartTime/Year)[1]', 'int') < 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)
Podría pensar / hacer lo siguiente:
Simplemente intenté obtener los datos en la configuración de informes serializados como columnas usando el xpath udf incorporado , sin embargo, no obtengo ningún registro, algunas pruebas son las siguientes:
seleccione xpath (SerializedReportSetting, '/ *') de ReportSetting límite 1;
seleccione xpath (SerializedReportSetting, '/ ReportSettings4') de ReportSetting límite 1;
seleccione xpath (SerializedReportSetting, '/ Trigger / Manual') de ReportSetting límite 1;
********** ACTUALIZACIÓN-1 **********
Usé regexp_replace para manejar el desafío anterior:
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;
En xpath dicen explícitamente:
La
xpath()función siempre devuelve una matriz de cadenas de colmena. Si la expresión da como resultado un valor que no es de texto (por ejemplo, otro nodo xml), la función devolverá una matriz vacía
Entonces puedes usar: xpath(SerializedReportSetting,'/ReportSettings4/Trigger/Manual/text()') from ReportSetting limit 1;
O una opción aún mejor es usar xpath_boolean/ xpath_int:
xpath_boolean : devuelve verdadero si la expresión XPath se evalúa como verdadera o si se encuentra un nodo coincidente.
xpath_boolean(SerializedReportSetting,'/ReportSettings4/Trigger/Manual') from ReportSetting limit 1;
xpath_short, xpath_int, xpath_long Estas funciones devuelven un valor numérico entero, o el valor cero si no se encuentra ninguna coincidencia, o si se encuentra una coincidencia pero el valor no es numérico. Se admiten operaciones matemáticas. En los casos en los que el valor sobrepasa el tipo de devolución, se devuelve el valor máximo para el tipo.
xpath_int(SerializedReportSetting,'/ReportSettings4/ReportPeriod/Month') from ReportSetting limit 1;
Este artículo se recopila de Internet, indique la fuente cuando se vuelva a imprimir.
En caso de infracción, por favor [email protected] Eliminar
Déjame decir algunas palabras