Title
s
Super Richman
04/27/2023, 7:29 AMHow can I use
LT JOINASOF JOINsSELECT * FROM (tt ASOF JOIN oil ASOF JOIN transactions ASOF JOIN holidays_events) LT JOIN
(SELECT * FROM (tt ASOF JOIN oil ASOF JOIN transactions ASOF JOIN holidays_events))right side column 'type' is of unsupported typeb
Bolek Ziobrowski
04/27/2023, 8:43 AMCould you please provide schema for both tables ?
s
Super Richman
04/27/2023, 9:00 AMCREATE TABLE 'tt' (
id INT,
date TIMESTAMP,
sf SYMBOL capacity 256 CACHE,
store_nbr INT,
family SYMBOL capacity 256 CACHE,
onpromotion INT,
sales DOUBLE
) timestamp (date) PARTITION BY MONTH WAL;CREATE TABLE 'oil' (date TIMESTAMP, dcoilwtico DOUBLE) timestamp (date) PARTITION BY MONTH WAL;CREATE TABLE 'transactions' (date TIMESTAMP, store_nbr INT, transactions INT) timestamp (date) PARTITION BY MONTH WAL;CREATE TABLE 'holidays_events' (
date TIMESTAMP,
type STRING,
locale STRING,
locale_name STRING,
description STRING,
transferred BOOLEAN
) timestamp (date) PARTITION BY MONTH WAL;b
Bolek Ziobrowski
04/27/2023, 9:11 AMIt looks like variable-length types can't be use in complex join that force result materialization .
If you switch string columns to symbol query does work, though, e.g.
CREATE TABLE holidays_events (
date TIMESTAMP,
type SYMBOL,
locale SYMBOL,
locale_name SYMBOL,
description SYMBOL,
transferred BOOLEAN
) timestamp (date) PARTITION BY MONTH WAL;You can also try the following query:
explain
select *
from
(
select *
from
(
SELECT tt1.*, tt2.*, tr1.*, he1.*, tt2.date as ts2
FROM tt tt1
lt join tt tt2
asof join transactions tr1
asof join holidays_events he1
) timestamp(ts2)
)
asof join transactions tr2
asof join holidays_events he2s
Super Richman
04/28/2023, 7:14 AMBolek thanks again! I will look into it