1NCE IoT SIMのData StreamerデータをAWS QuickSightで可視化してみる

Filename : cdr_20221128_0434411.csv

"id","event_start_timestamp","event_stop_timestamp","organisation_id","organisation_name","endpoint_id","sim_id","iccid","imsi","operator_id","operator_name","country_id","operator_country_name","traffic_type_id","traffic_type_description","volume","volume_tx","volume_rx","cost","currency_id","currency_code","currency_symbol","ratezone_tariff_id","ratezone_tariff_name","ratezone_id","ratezone_name","endpoint_name","endpoint_ip_address","endpoint_tags","endpoint_imei","msisdn_msisdn","sim_production_date","operator_mncs","country_mcc"
"xxxxxxxxxxxxxxxx","2022-11-28 04:28:56","2022-11-28 04:33:52","xxxxxxx","xxxxxxxxx","xxxxxxx","xxxxxxxx","xxxxxxxxxxxxxx","xxxxxxxxxxxx","681","SoftBank","100","Japan","5","Data","0.294108","0.057222","0.236886","0.294108","1","JPY","￥","442","1NCE Production 01 - 1Mbps","2173","Rate Zone 3 (RoW)","xxxxxxxxxxxxx","10.208.xxx.xxx",,,"xxxxxxxxxxxxxx","2022-01-17 08:54:06","20","440"

Filename : event_20221125_093657.csv

"id","timestamp","event_type_id","event_type_description","event_severity_id","event_severity_description","organisation_id","organisation_name","description","alert","event_source_id","event_source_name","endpoint_id","endpoint_name","endpoint_imei","endpoint_ip_address","endpoint_tags","sim_id","sim_iccid","msisdn_msisdn","sim_production_date","imsi_id","imsi_imsi","user_id","user_username","user_name"
"xxxxxxxxxxxxx","2022-11-25 09:36:57","1","Update location","0","INFO","xxxxxxx","xxxxxxxxxxx","New location received from VLR for IMSI=xxxxxxxxxxxxxxxxx, now attached to VLR=xxxxxxxxxxxxx.","0","0","Network","xxxxxxxxxxx","xxxxxxxxxxxxxx","xxxxxxxxxxxxx","10.208.xx.xxx",,"xxxxxxxxxx","xxxxxxxxxxxxxxxxx","xxxxxxxxxxxxxxxxx","2021-03-22 08:17:38","xxxxxxxxx","xxxxxxxxxxxxxxxxxx",,,

CREATE EXTERNAL TABLE IF NOT EXISTS `<DATABASE_NAME>`.`<TABLE_NAME>` (
  `id` bigint,
  `event_start_timestamp` string,
  `event_stop_timestamp` string,
  `organisation_id` int,
  `organisation_name` int,
  `endpoint_id` int,
  `sim_id` string,
  `iccid` string,
  `imsi` string,
  `operator_id` int,
  `operator_name` string,
  `country_id` int,
  `operator_country_name` string,
  `traffic_type_id` int,
  `traffic_type_description` string,
  `volume` float,
  `volume_tx` float,
  `volume_rx` float,
  `cost` float,
  `currency_id` int,
  `currency_code` string,
  `currency_symbol` string,
  `ratezone_tariff_id` int,
  `ratezone_tariff_name` string,
  `ratezone_id` int,
  `ratezone_name` string,
  `endpoint_name` bigint,
  `endpoint_ip_address` string,
  `endpoint_tags` string,
  `endpoint_imei` string,
  `msisdn_msisdn` string,
  `sim_production_date` string,
  `operator_mncs` int,
  `country_mcc` int
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = ',',
   'quoteChar' = '"',
   'escapeChar' = '\\'
   )
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://xxxxxx<S3-LOCATION>xxxxxxxx'
TBLPROPERTIES ('classification' = 'csv', 'skip.header.line.count'='1');

SELECT * FROM "<DATABASE_NAME>"."<TABLE_NAME>";