问题

I am using Databricks.

我正在使用Databricks。

I have Table 1 which has several columns and an array column.

我有一个名为“Table 1”的表，其中包含多个列和一个数组列。

I have Table 2 which has the same columns as Table 1 except for the array column. For Table 2 the array column is not applicable.

我有一个名为“Table 2”的表，它与“Table 1”具有相同的列，除了数组列。对于“Table 2”，数组列不适用。

I want to combine these tables but I have been failing so far. I have tried UNION ALL but that still gives me an error: Union can only be performed on tables with the compatible column types.

我想要合并这些表，但到目前为止我一直失败了。我尝试过UNION ALL，但仍然出现错误：“只能在具有兼容列类型的表上执行Union操作。”

I have also tried to populate null in the Table2 array column as shown below. This throws the same error.

我还尝试在Table2的数组列中填充null，如下所示。这会引发相同的错误。

Is there a solution beyond creating a "fake" array in Table2 so I can perform this union? I was always under the assumption union all would exempt the union from data type checks but that's obviously not the case.

除了在Table2中创建一个“虚假”数组以执行此联合操作之外，是否还有其他解决方法？我一直以为union all会豁免联合操作的数据类型检查，但显然不是这样。

select
col1,
col2,
arraycol
from table1

union all

select
col1,
col2,
null as arraycol
from table2

答案1

得分: 1

你可以尝试用特定数据类型的空数组替换空值，该数组与你的数组填充的数据类型相匹配：

select col1,
col2,
arraycol
from table1

union all

select col1,
col2,
from_json(""[]"", ""array<your_data_type>"") as arraycol
from table2

<details>
<summary>英文:</summary>

You can try substituting your null value with empty arrays of the specific datatype your array is filled with:

select col1,
col2,
arraycol
from table1

union all

select col1,
col2,
from_json("[]", "array<your_data_type>") as arraycol
from table2

</details>