SamGreene
Contributor II
In response to Wojciech_BUK

‎12-21-2023 03:26 PM

Thanks for your advice.  I have two statements in the notebook regarding these tables and no references later.  Here is the code for reference, maybe I have something wrong here...

 
/*Alarm Data */
CREATE OR REFRESH STREAMING TABLE RNI_Alarms_Raw(
CONSTRAINT timestamp_is_not_null EXPECT (TimeStamp IS NOT NULL),
CONSTRAINT meterid_is_not_null EXPECT (MeterID IS NOT NULL),
CONSTRAINT commodity_is_not_null EXPECT (Commodity IS NOT NULL)
)
TBLPROPERTIES ("quality" = "bronze")
AS
SELECT *, _metadata.file_path as file_path
FROM STREAM read_files(
  '/Volumes/ami/landing/rni/Alarm/', format => 'CSV', schema => 'RecordType string, RecordVersion string, SenderID string, SenderCustomerID string,ReceiverID string,ReceiverCustomerID string,TimeStamp string,MeterID string,Purpose string,Commodity string,Units string,CalculationConstant int,Interval int,Count int,Data1 string,Data2 string,Data3 string');

/* 2 */
CREATE OR REFRESH LIVE TABLE RNI_Alarms(
  CONSTRAINT timestamp_is_not_null EXPECT (TimeStamp IS NOT NULL),
  CONSTRAINT meterid_is_not_null EXPECT (MeterID IS NOT NULL),
  CONSTRAINT commodity_is_not_null EXPECT (Commodity IS NOT NULL),
  CONSTRAINT alarm_code_is_valid EXPECT (AlarmID < 65) ON VIOLATION DROP ROW
)
TBLPROPERTIES ("quality" = "silver")
AS
SELECT
  RecordType,
  RecordVersion,
  SenderID,
  SenderCustomerID,
  ReceiverID,
  ReceiverCustomerID,
  CAST(TO_TIMESTAMP(TimeStamp, 'yyyyMMddHHmm') AS timestamp) as TimeStamp,
  MeterID,
  Purpose,
  Commodity,
  Units,
  CalculationConstant,
  Interval,
  Count,
  CAST(TO_TIMESTAMP(Data1, 'yyyyMMddHHmm') AS timestamp) as AlarmTimestamp,
  Data2 AlarmCodeSet,
  Data3 AlarmID
FROM LIVE.RNI_Alarms_Raw
where CAST(TO_TIMESTAMP(Data1, 'yyyyMMddHHmm') AS timestamp) > '2023-12-01 00:00:00';

 Regarding streaming being ok in some cases, this scenario is probably one of them as the data is append-only, and I'm not creating aggregates, but I wanted to see how to make the change on something simple. 

0 Kudos