I am playing around with PDTs and running into an error on the table create. This is a memSQL database…
derived_table: {
create_process: {
sql_step: CREATE TABLE ${SQL_TABLE_NAME}
(start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
stream_id INT UNSIGNED NOT NULL,
program_identifier SMALLINT UNSIGNED,
device_identifier INT UNSIGNED,
input_information VARCHAR(255),
input_type VARCHAR(255),
encoder_decoder VARCHAR(255),
encoder_decoder_profile VARCHAR(255),
chroma_subsampling VARCHAR(255),
expected_frame_rate DECIMAL(10,3) UNSIGNED,
frame_latency_millisec DECIMAL(10,6),
horizontal_resolution_pixels SMALLINT UNSIGNED,
vertical_resolution_pixels SMALLINT UNSIGNED,
KEY pk_stream_id_start_date_end_date (
stream_id,
start_date,
end_date
)
) ;;
sql_step: INSERT INTO ${SQL_TABLE_NAME} (
SELECT
meta_data.effective_time AS start_date,
CASE WHEN (lead(meta_data.effective_time) over (partition by meta_data.stream_id) IS NULL) THEN CONVERT("9999-12-31 23:59:59",DATETIME)
ELSE lead(meta_data.effective_time) over (partition by meta_data.stream_id) END AS end_date,
meta_data.stream_id,
meta_data.program_identifier,
meta_data.device_identifier,
meta_data.input_information,
meta_data.input_type,
meta_data.encoder_decoder,
meta_data.encoder_decoder_profile,
meta_data.chroma_subsampling,
meta_data.expected_frame_rate,
meta_data.frame_latency_millisec,
meta_data.horizontal_resolution_pixels,
meta_data.vertical_resolution_pixels
FROM
(SELECT
effective_time,
stream_id,
program_identifier,
device_identifier,
input_information,
input_type,
encoder_decoder,
encoder_decoder_profile,
chroma_subsampling,
expected_frame_rate,
frame_latency_millisec,
horizontal_resolution_pixels,
vertical_resolution_pixels
FROM
live_raw_meta_video_stream AS e
ORDER BY stream_id,effective_time
) AS meta_data)
;;
sql_step: ANALYZE ${SQL_TABLE_NAME} ;;
}
If I manually run the exact SQL generated by the explore the syntax work fine in MEMSQL studio…Is there some validation using MySQL syntax ?
