def test_adjust_timestamps_in_record_unexpected_int_will_raise_exception(self):
"""Test if timestamps converted to the acceptable valid ranges"""
record = {
'key': 100,
}
schema = {
'properties': {
'key': {'type': ['null', 'string'], 'format': 'date'},
}
}
with self.assertRaises(UnexpectedValueTypeException):
stream_utils.adjust_timestamps_in_record(record, schema)
def test_adjust_timestamps_in_record(self):
"""Test if timestamps converted to the acceptable valid ranges"""
record = {
'key1': '1',
'key2': '2030-01-22',
'key3': '10000-01-22 12:04:22',
'key4': '25:01:01',
'key5': 'I\'m good',
'key6': None,
}
schema = {
'properties': {
'key1': {
'type': ['null', 'string', 'integer'],
},
'key2': {
'anyOf': [
{'type': ['null', 'string'], 'format': 'date'},
{'type': ['null', 'string']}
]
},
'key3': {
'type': ['null', 'string'], 'format': 'date-time',
},
'key4': {
'anyOf': [
{'type': ['null', 'string'], 'format': 'time'},
{'type': ['null', 'string']}
]
},
'key5': {
'type': ['null', 'string'],
},
'key6': {
'type': ['null', 'string'], 'format': 'time',
},
}
}
stream_utils.adjust_timestamps_in_record(record, schema)
self.assertEqual(record, {
'key1': '1',
'key2': '2030-01-22',
'key3': '9999-12-31 23:59:59.999999',
'key4': '23:59:59.999999',
'key5': 'I\'m good',
'key6': None
})
def persist_lines(config, lines, table_cache=None, file_format_type: FileFormatTypes = None) -> None:
"""Main loop to read and consume singer messages from stdin
Params:
config: configuration dictionary
lines: iterable of singer messages
table_cache: Optional dictionary of Snowflake table structures. This is useful to run the less
INFORMATION_SCHEMA and SHOW queries as possible.
If not provided then an SQL query will be generated at runtime to
get all the required information from Snowflake
file_format_type: Optional FileFormatTypes value that defines which supported file format to use
to load data into Snowflake.
If not provided then it will be detected automatically
Returns:
tuple of retrieved items: table_cache, file_format_type
"""
state = None
flushed_state = None
schemas = {}
key_properties = {}
validators = {}
records_to_load = {}
row_count = {}
stream_to_sync = {}
total_row_count = {}
batch_size_rows = config.get('batch_size_rows', DEFAULT_BATCH_SIZE_ROWS)
batch_wait_limit_seconds = config.get('batch_wait_limit_seconds', None)
flush_timestamp = datetime.utcnow()
archive_load_files = config.get('archive_load_files', False)
archive_load_files_data = {}
# Loop over lines from stdin
for line in lines:
try:
o = json.loads(line)
except json.decoder.JSONDecodeError:
LOGGER.error('Unable to parse:\n%s', line)
raise
if 'type' not in o:
raise Exception(f"Line is missing required key 'type': {line}")
t = o['type']
if t == 'RECORD':
if 'stream' not in o:
raise Exception(f"Line is missing required key 'stream': {line}")
if o['stream'] not in schemas:
raise Exception(
f"A record for stream {o['stream']} was encountered before a corresponding schema")
# Get schema for this record's stream
stream = o['stream']
stream_utils.adjust_timestamps_in_record(o['record'], schemas[stream])
# Validate record
if config.get('validate_records'):
try:
validators[stream].validate(stream_utils.float_to_decimal(o['record']))
except Exception as ex:
if type(ex).__name__ == "InvalidOperation":
raise InvalidValidationOperationException(
f"Data validation failed and cannot load to destination. RECORD: {o['record']}\n"
"multipleOf validations that allows long precisions are not supported (i.e. with 15 digits"
"or more) Try removing 'multipleOf' methods from JSON schema.") from ex
raise RecordValidationException(f"Record does not pass schema validation. RECORD: {o['record']}") \
from ex
primary_key_string = stream_to_sync[stream].record_primary_key_string(o['record'])
if not primary_key_string:
primary_key_string = 'RID-{}'.format(total_row_count[stream])
if stream not in records_to_load:
records_to_load[stream] = {}
# increment row count only when a new PK is encountered in the current batch
if primary_key_string not in records_to_load[stream]:
row_count[stream] += 1
total_row_count[stream] += 1
# append record
if config.get('add_metadata_columns') or config.get('hard_delete'):
records_to_load[stream][primary_key_string] = stream_utils.add_metadata_values_to_record(o)
else:
records_to_load[stream][primary_key_string] = o['record']
if archive_load_files and stream in archive_load_files_data:
# Keep track of min and max of the designated column
stream_archive_load_files_values = archive_load_files_data[stream]
if 'column' in stream_archive_load_files_values:
incremental_key_column_name = stream_archive_load_files_values['column']
incremental_key_value = o['record'][incremental_key_column_name]
min_value = stream_archive_load_files_values['min']
max_value = stream_archive_load_files_values['max']
if min_value is None or min_value > incremental_key_value:
stream_archive_load_files_values['min'] = incremental_key_value
if max_value is None or max_value < incremental_key_value:
stream_archive_load_files_values['max'] = incremental_key_value
flush = False
if row_count[stream] >= batch_size_rows:
flush = True
LOGGER.info("Flush triggered by batch_size_rows (%s) reached in %s",
batch_size_rows, stream)
elif (batch_wait_limit_seconds and
datetime.utcnow() >= (flush_timestamp + timedelta(seconds=batch_wait_limit_seconds))):
flush = True
LOGGER.info("Flush triggered by batch_wait_limit_seconds (%s)",
batch_wait_limit_seconds)
if flush:
# flush all streams, delete records if needed, reset counts and then emit current state
if config.get('flush_all_streams'):
filter_streams = None
else:
filter_streams = [stream]
# Flush and return a new state dict with new positions only for the flushed streams
flushed_state = flush_streams(
records_to_load,
row_count,
stream_to_sync,
config,
state,
flushed_state,
archive_load_files_data,
filter_streams=filter_streams)
flush_timestamp = datetime.utcnow()
# emit last encountered state
emit_state(copy.deepcopy(flushed_state))
elif t == 'SCHEMA':
if 'stream' not in o:
raise Exception(f"Line is missing required key 'stream': {line}")
stream = o['stream']
new_schema = stream_utils.float_to_decimal(o['schema'])
# Update and flush only if the the schema is new or different than
# the previously used version of the schema
if stream not in schemas or schemas[stream] != new_schema:
schemas[stream] = new_schema
validators[stream] = Draft7Validator(schemas[stream], format_checker=FormatChecker())
# flush records from previous stream SCHEMA
# if same stream has been encountered again, it means the schema might have been altered
# so previous records need to be flushed
if row_count.get(stream, 0) > 0:
flushed_state = flush_streams(records_to_load,
row_count,
stream_to_sync,
config,
state,
flushed_state,
archive_load_files_data)
# emit latest encountered state
emit_state(flushed_state)
# key_properties key must be available in the SCHEMA message.
if 'key_properties' not in o:
raise Exception("key_properties field is required")
# Log based and Incremental replications on tables with no Primary Key
# cause duplicates when merging UPDATE events.
# Stop loading data by default if no Primary Key.
#
# If you want to load tables with no Primary Key:
# 1) Set ` 'primary_key_required': false ` in the target-snowflake config.json
# or
# 2) Use fastsync [postgres-to-snowflake, mysql-to-snowflake, etc.]
if config.get('primary_key_required', True) and len(o['key_properties']) == 0:
LOGGER.critical('Primary key is set to mandatory but not defined in the [%s] stream', stream)
raise Exception("key_properties field is required")
key_properties[stream] = o['key_properties']
if config.get('add_metadata_columns') or config.get('hard_delete'):
stream_to_sync[stream] = DbSync(config,
add_metadata_columns_to_schema(o),
table_cache,
file_format_type)
else:
stream_to_sync[stream] = DbSync(config, o, table_cache, file_format_type)
if archive_load_files:
archive_load_files_data[stream] = {
'tap': config.get('tap_id'),
}
# In case of incremental replication, track min/max of the replication key.
# Incremental replication is assumed if o['bookmark_properties'][0] is one of the columns.
incremental_key_column_name = stream_utils.get_incremental_key(o)
if incremental_key_column_name:
LOGGER.info("Using %s as incremental_key_column_name", incremental_key_column_name)
archive_load_files_data[stream].update(
column=incremental_key_column_name,
min=None,
max=None
)
else:
LOGGER.warning(
"archive_load_files is enabled, but no incremental_key_column_name was found. "
"Min/max values will not be added to metadata for stream %s.", stream
)
stream_to_sync[stream].create_schema_if_not_exists()
stream_to_sync[stream].sync_table()
row_count[stream] = 0
total_row_count[stream] = 0
elif t == 'ACTIVATE_VERSION':
LOGGER.debug('ACTIVATE_VERSION message')
elif t == 'STATE':
LOGGER.debug('Setting state to %s', o['value'])
state = o['value']
# Initially set flushed state
if not flushed_state:
flushed_state = copy.deepcopy(state)
else:
raise Exception(f"Unknown message type {o['type']} in message {o}")
# if some bucket has records that need to be flushed but haven't reached batch size
# then flush all buckets.
if sum(row_count.values()) > 0:
# flush all streams one last time, delete records if needed, reset counts and then emit current state
flushed_state = flush_streams(records_to_load, row_count, stream_to_sync, config, state, flushed_state,
archive_load_files_data)
# emit latest state
emit_state(copy.deepcopy(flushed_state))