def cast(self, field_type: 'FieldType') -> 'RecordsSchemaFieldConstraints': from .integer import RecordsSchemaFieldIntegerConstraints from .decimal import RecordsSchemaFieldDecimalConstraints from .string import RecordsSchemaFieldStringConstraints required = self.required unique = self.unique constraints: RecordsSchemaFieldConstraints if field_type == 'integer': constraints =\ RecordsSchemaFieldIntegerConstraints(required=required, unique=unique, min_=None, max_=None) elif field_type == 'string': constraints =\ RecordsSchemaFieldStringConstraints(required=required, unique=unique, max_length_bytes=None, max_length_chars=None) elif field_type == 'decimal': constraints =\ RecordsSchemaFieldDecimalConstraints(required=required, unique=unique) elif (field_type == 'boolean' or field_type == 'date' or field_type == 'time' or field_type == 'timetz' or field_type == 'datetime' or field_type == 'datetimetz'): constraints =\ RecordsSchemaFieldConstraints(required=required, unique=unique) else: _assert_never( field_type, 'Teach me how to downcast constraints ' f'for {field_type}') return constraints
def rewound_decompressed_fileobj( fileobj: IO[bytes], compression: HintCompression) -> Iterator[IO[bytes]]: with rewound_fileobj(fileobj) as fileobj_after_rewind: if compression is None: yield fileobj elif compression == 'GZIP': yield gzip.GzipFile(mode='rb', fileobj=fileobj_after_rewind) # type: ignore elif compression == 'LZO': # This might be useful to implement this: # https://github.com/ir193/python-lzo/blob/master/lzo.py#L44 raise NotImplementedError( 'Records mover does not currently know how ' 'to decompress LZO files for inspection') elif compression == 'BZIP': yield bz2.BZ2File(mode='rb', filename=fileobj_after_rewind) else: _assert_never(compression)
def add_load_job_csv_config(unhandled_hints: Set[str], hints: ValidatedRecordsHints, fail_if_cant_handle_hint: bool, config: bigquery.LoadJobConfig) -> None: # source_format: File format of the data. config.source_format = 'CSV' # encoding: The character encoding of the data. # The supported values are UTF-8 or ISO-8859-1. # "UTF-8 or ISO-8859-1" # if hints.encoding == 'UTF8': config.encoding = 'UTF-8' else: # Currently records hints don't support ISO-8859-1 cant_handle_hint(fail_if_cant_handle_hint, 'encoding', hints) quiet_remove(unhandled_hints, 'encoding') # field_delimiter: The separator for fields in a CSV file. assert isinstance(hints.field_delimiter, str) config.field_delimiter = hints.field_delimiter quiet_remove(unhandled_hints, 'field-delimiter') # allow_jagged_rows: Allow missing trailing optional columns (CSV only). # null_marker: Represents a null value (CSV only) # # (documentation is mangled for this one, but I assume the default is # '' or something sensible, so not messing with it) # quote_character: Character used to quote data sections (CSV # only). # # [Optional] The value that is used to quote data sections in # a CSV file. BigQuery converts the string to ISO-8859-1 # encoding, and then uses the first byte of the encoded string # to split the data in its raw, binary state. The default # value is a double-quote ('"'). If your data does not contain # quoted sections, set the property value to an empty # string. If your data contains quoted newline characters, you # must also set the allowQuotedNewlines property to # true. # # @default " # I tried a few combinations and found that when you leave quote_character as the default # # * Fields quoted with "" are loaded without the surrounding quotes in the # string # * "" becomes " in a quoted field # * "" stays "" in a non-quoted field # * nonnumeric quoting works fine # * full quoting works fine if hints.quoting is None: config.quote_character = '' elif hints.quoting == 'all' or hints.quoting == 'minimal' or hints.quoting == 'nonnumeric': # allow_quoted_newlines: Allow quoted data containing newline # characters (CSV only). config.allow_quoted_newlines = True assert isinstance(hints.quotechar, str) config.quote_character = hints.quotechar if hints.doublequote: pass else: cant_handle_hint(fail_if_cant_handle_hint, 'doublequote', hints) else: _assert_never(hints.quoting) quiet_remove(unhandled_hints, 'quoting') quiet_remove(unhandled_hints, 'quotechar') quiet_remove(unhandled_hints, 'doublequote') # No mention of escaping in BigQuery documentation, and in # practice backslashes come through without being interpreted. if hints.escape is None: pass else: cant_handle_hint(fail_if_cant_handle_hint, 'escape', hints) quiet_remove(unhandled_hints, 'escape') # skip_leading_rows: Number of rows to skip when reading data (CSV only). if hints.header_row: config.skip_leading_rows = 1 else: config.skip_leading_rows = 0 quiet_remove(unhandled_hints, 'header-row') # "When you load CSV or JSON data, values in DATE columns must # use the dash (-) separator and the date must be in the # following format: YYYY-MM-DD (year-month-day)." if hints.dateformat == 'YYYY-MM-DD': pass else: cant_handle_hint(fail_if_cant_handle_hint, 'dateformat', hints) quiet_remove(unhandled_hints, 'dateformat') # "When you load JSON or CSV data, values in TIMESTAMP columns # must use a dash (-) separator for the date portion of the # timestamp, and the date must be in the following format: # YYYY-MM-DD (year-month-day). The hh:mm:ss # (hour-minute-second) portion of the timestamp must use a # colon (:) separator." # # # To test, log into BigQuery web console and try SQL like this # (assumption is that the same timestamp parser is used during # CSV loads) # # select TIMESTAMP("2000-01-02 16:34:56.789012US/Eastern") as a; # # Tests performed and result displayed on console query: # # DATE: # * 01-02-2019 (rejected): # * 01/02/19 (rejected): # * 2019-01-01 (accepted): 2019-01-01 # DATETIME: # * 2019-01-01 1:00pm (rejected): # * 2019-01-01 1:00:00pm (rejected) # * 2019-01-01 1:00PM (rejected): # * 2019-01-01 13:00 (rejected): # * 2019-01-01 13:00:00 (accepted): 2019-01-01T13:00:00 # * 2019-01-01 1:00pm US/Eastern (rejected): # * 2019-01-01 1:00:00pm US/Eastern (rejected): # * 2019-01-01 13:00:00 US/Eastern (rejected): # * 2019-01-01 13:00:00 EST (rejected): # * 1997-12-17 07:37:16-08 (rejected) # * 2019-01-01T13:00:00 (accepted): 2019-01-01T13:00:00 # # TIME: # * 1:00pm (rejected): # * 1:00:00pm (rejected): # * 13:00 (rejected): # * 13:00:00 (accepted): 13:00:00 # * 1:00pm US/Eastern (rejected): # * 1:00pm EST (rejected): # * 07:37:16-08 (rejected): # # TIMESTAMP ("Required format is YYYY-MM-DD # HH:MM[:SS[.SSSSSS]]", which is BS, as it doesn't specify the # timezone format): # # * 2019-01-01 1:00pm (rejected): # * 2019-01-01 1:00:00pm (rejected) # * 2019-01-01 1:00PM (rejected): # * 2019-01-01 13:00 (rejected): # * 2019-01-01 13:00:00 (accepted): 2019-01-01T13:00:00 # * 2019-01-01 1:00pm US/Eastern (rejected): # * 2019-01-01 1:00:00pm US/Eastern (rejected): # * 2019-01-01 13:00:00 US/Eastern (rejected): # * 2019-01-01 13:00:00 EST (rejected): # * 1997-12-17 07:37:16-08 (accepted): 1997-12-17 15:37:16 UTC # * 2019-01-01T13:00:00-08 (accepted): 2019-01-01 21:00:00 UTC # * 2000-01-02 16:34:56.789012+0000 (rejected) # * 2000-01-02 16:34:56.789012+00:00 (accepted) # * 2000-01-02 16:34:56.789012EST (rejected) # * 2000-01-02 16:34:56.789012US/Eastern (rejected) # * 2000-01-02 16:34:56.789012UTC (accepted): 2000-01-02 16:34:56.789012 UTC # * 2000-01-02 16:34:56.789012 UTC (accepted: 2000-01-02 16:34:56.789012 UTC # # https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#examples # # https://stackoverflow.com/questions/47466296/bigquery-datetime-format-csv-to-bigquery-yyyy-mm-dd-hhmmss-ssssss # # BigQuery supports exactly one format of ingesting timestamps # with timezones (what they call 'TIMESTAMP' they call timestamps # without timezones 'DATETIME'. # # That format they accept is ISO 8601, which sounds all nice and # standardy. Usable timestamps look like 2000-01-02 # 16:34:56.789012+00:00. # Cool cool. The only issue is that Python's strftime doesn't # actually provide a way to add the ':' in the timezone # offset. The only timezone offset code, %z, does not provide the # colon. Other implementations (GNU libc) offers the %:z option, # but that doesn't exist in Python and thus in Pandas. # # So if you're using Python to export timestamps with timezones, # you should probably use the `YYYY-MM-DD HH24:MI:SS` format and # express them in UTC. # # https://stackoverflow.com/questions/44836581/does-python-time-strftime-process-timezone-options-correctly-for-rfc-3339 # https://stackoverflow.com/questions/28729212/pandas-save-date-in-iso-format # if hints.datetimeformat in ['YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH:MI:SS']: pass else: cant_handle_hint(fail_if_cant_handle_hint, 'datetimeformat', hints) quiet_remove(unhandled_hints, 'datetimeformat') if hints.datetimeformattz in ['YYYY-MM-DD HH:MI:SSOF', 'YYYY-MM-DD HH24:MI:SSOF', 'YYYY-MM-DD HH:MI:SS']: pass else: cant_handle_hint(fail_if_cant_handle_hint, 'datetimeformattz', hints) quiet_remove(unhandled_hints, 'datetimeformattz') if hints.timeonlyformat in ['HH24:MI:SS', 'HH:MI:SS']: pass else: cant_handle_hint(fail_if_cant_handle_hint, 'timeonlyformat', hints) quiet_remove(unhandled_hints, 'timeonlyformat') # No options to change this. Tested with unix newlines, dos # newlines and mac newlines and all were understood.: if hints.record_terminator in ['\n', '\r\n', '\r', None]: pass else: cant_handle_hint(fail_if_cant_handle_hint, 'record-terminator', hints) quiet_remove(unhandled_hints, 'record-terminator') # No way to flag compression, but tested uncompressed, with # gzip and works great. .bz2 gives "400 Unsupported # compression type". Not sure about .lzo, but pandas can't # handle it regardless, so doubt it's handled. if hints.compression is None or hints.compression == 'GZIP': pass else: cant_handle_hint(fail_if_cant_handle_hint, 'compression', hints) quiet_remove(unhandled_hints, 'compression')
def redshift_unload_options( unhandled_hints: Set[str], records_format: BaseRecordsFormat, fail_if_cant_handle_hint: bool) -> RedshiftUnloadOptions: redshift_options: RedshiftUnloadOptions = {} if isinstance(records_format, ParquetRecordsFormat): redshift_options['format'] = Format.parquet return redshift_options if not isinstance(records_format, DelimitedRecordsFormat): raise NotImplementedError( "Redshift export only supported via Parquet and " "delimited currently") hints = records_format.\ validate(fail_if_cant_handle_hint=fail_if_cant_handle_hint) if hints.escape == '\\': redshift_options['escape'] = True elif hints.escape is None: pass else: _assert_never(hints.escape) quiet_remove(unhandled_hints, 'escape') redshift_options['delimiter'] = hints.field_delimiter quiet_remove(unhandled_hints, 'field-delimiter') if hints.record_terminator == "\n": # This is Redshift's one and only export format pass else: cant_handle_hint(fail_if_cant_handle_hint, 'record-terminator', hints) quiet_remove(unhandled_hints, 'record-terminator') if hints.quoting == 'all': if hints.doublequote is not False: cant_handle_hint(fail_if_cant_handle_hint, 'doublequote', hints) if hints.quotechar != '"': cant_handle_hint(fail_if_cant_handle_hint, 'quotechar', hints) redshift_options['add_quotes'] = True elif hints.quoting is None: redshift_options['add_quotes'] = False else: cant_handle_hint(fail_if_cant_handle_hint, 'quoting', hints) quiet_remove(unhandled_hints, 'quoting') quiet_remove(unhandled_hints, 'doublequote') quiet_remove(unhandled_hints, 'quotechar') if hints.compression == 'GZIP': redshift_options['gzip'] = True elif hints.compression is None: # good to go pass else: cant_handle_hint(fail_if_cant_handle_hint, 'compression', hints) quiet_remove(unhandled_hints, 'compression') if hints.encoding != 'UTF8': cant_handle_hint(fail_if_cant_handle_hint, 'encoding', hints) quiet_remove(unhandled_hints, 'encoding') if hints.datetimeformattz not in [ 'YYYY-MM-DD HH:MI:SSOF', 'YYYY-MM-DD HH24:MI:SSOF' ]: cant_handle_hint(fail_if_cant_handle_hint, 'datetimeformattz', hints) quiet_remove(unhandled_hints, 'datetimeformattz') if hints.datetimeformat not in [ 'YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH:MI:SS' ]: cant_handle_hint(fail_if_cant_handle_hint, 'datetimeformat', hints) quiet_remove(unhandled_hints, 'datetimeformat') if hints.dateformat != 'YYYY-MM-DD': cant_handle_hint(fail_if_cant_handle_hint, 'dateformat', hints) quiet_remove(unhandled_hints, 'dateformat') # Redshift doesn't have a time without date type, so there's # nothing that could be exported there. quiet_remove(unhandled_hints, 'timeonlyformat') if hints.header_row: # Redshift unload doesn't know how to add headers on an # unload. Bleh. # https://stackoverflow.com/questions/24681214/unloading-from-redshift-to-s3-ith-headers cant_handle_hint(fail_if_cant_handle_hint, 'header-row', hints) else: quiet_remove(unhandled_hints, 'header-row') return redshift_options
def redshift_copy_options( unhandled_hints: Set[str], records_format: BaseRecordsFormat, fail_if_cant_handle_hint: bool, fail_if_row_invalid: bool, max_failure_rows: Optional[int]) -> RedshiftCopyOptions: redshift_options: RedshiftCopyOptions = {} if isinstance(records_format, AvroRecordsFormat): redshift_options['format'] = Format.avro return redshift_options if not isinstance(records_format, DelimitedRecordsFormat): raise NotImplementedError(f"Teach me how to COPY to {records_format}") hints = records_format.\ validate(fail_if_cant_handle_hint=fail_if_cant_handle_hint) if hints.compression == 'GZIP': redshift_options['compression'] = Compression.gzip elif hints.compression == 'LZO': redshift_options['compression'] = Compression.lzop elif hints.compression == 'BZIP': redshift_options['compression'] = Compression.bzip2 elif hints.compression is None: redshift_options['compression'] = None else: _assert_never(hints.compression) quiet_remove(unhandled_hints, 'compression') if hints.dateformat is None: redshift_options['date_format'] = 'auto' else: redshift_options['date_format'] = hints.dateformat quiet_remove(unhandled_hints, 'dateformat') if hints.encoding == 'UTF8': redshift_options['encoding'] = Encoding.utf8 elif hints.encoding == 'UTF16': redshift_options['encoding'] = Encoding.utf16 elif hints.encoding == 'UTF16LE': redshift_options['encoding'] = Encoding.utf16le elif hints.encoding == 'UTF16BE': redshift_options['encoding'] = Encoding.utf16be else: cant_handle_hint(fail_if_cant_handle_hint, 'encoding', hints) redshift_options['encoding'] = Encoding(hints.encoding) quiet_remove(unhandled_hints, 'encoding') redshift_options['quote'] = hints.quotechar quiet_remove(unhandled_hints, 'quotechar') if hints.quoting == 'minimal': if hints.escape is not None: cant_handle_hint(fail_if_cant_handle_hint, 'escape', hints) if hints.field_delimiter != ',': cant_handle_hint(fail_if_cant_handle_hint, 'field-delimiter', hints) if hints.doublequote is not True: cant_handle_hint(fail_if_cant_handle_hint, 'doublequote', hints) redshift_options['format'] = Format.csv else: redshift_options['delimiter'] = hints.field_delimiter if hints.escape == '\\': redshift_options['escape'] = True elif hints.escape is None: redshift_options['escape'] = False else: _assert_never(hints.escape) if hints.quoting == 'all': redshift_options['remove_quotes'] = True if hints.doublequote is not False: cant_handle_hint(fail_if_cant_handle_hint, 'doublequote', hints) elif hints.quoting is None: redshift_options['remove_quotes'] = False else: cant_handle_hint(fail_if_cant_handle_hint, 'quoting', hints) quiet_remove(unhandled_hints, 'quoting') quiet_remove(unhandled_hints, 'escape') quiet_remove(unhandled_hints, 'field-delimiter') quiet_remove(unhandled_hints, 'doublequote') if hints.datetimeformat is None: redshift_options['time_format'] = 'auto' else: # After testing, Redshift's date/time parsing doesn't actually # support timezone parsing if you give it configuration - as # documented below, it doesn't accept a time zone as part of # the format string, and in experimentation, it silently drops # the offset when data into a timestamptz field if you specify # one directly. # # Its automatic parser seems to be smarter, though, and is # likely to handle a variety of formats: # # https://docs.aws.amazon.com/redshift/latest/dg/automatic-recognition.html if hints.datetimeformat != hints.datetimeformattz: # The Redshift auto parser seems to take a good handling # at our various supported formats, so let's give it a # shot if we're not able to specify a specific format due # to the Redshift timestamptz limitation: # # analytics=> create table formattest (test char(32)); # CREATE TABLE # analytics=> insert into formattest values('2018-01-01 12:34:56'); # INSERT 0 1 # analytics=> insert into formattest values('01/02/18 15:34'); # INSERT 0 1 # analytics=> insert into formattest values('2018-01-02 15:34:12'); # INSERT 0 1 # analytics=> insert into formattest values('2018-01-02 10:34 PM'); # INSERT 0 1 # analytics=> select test, cast(test as timestamp) as timestamp, # cast(test as date) as date from formattest; # # test | timestamp | date # ----------------------------------+---------------------+------------ # 2018-01-01 12:34:56 | 2018-01-01 12:34:56 | 2018-01-01 # 01/02/18 15:34 | 2018-01-02 15:34:00 | 2018-01-02 # 2018-01-02 15:34:12 | 2018-01-02 15:34:12 | 2018-01-02 # 2018-01-02 10:34 PM | 2018-01-02 22:34:00 | 2018-01-02 # (4 rows) # # analytics=> redshift_options['time_format'] = 'auto' else: redshift_options['time_format'] = hints.datetimeformat quiet_remove(unhandled_hints, 'datetimeformat') quiet_remove(unhandled_hints, 'datetimeformattz') # Redshift doesn't support time-only fields, so these will # come in as strings regardless. quiet_remove(unhandled_hints, 'timeonlyformat') if max_failure_rows is not None: redshift_options['max_error'] = max_failure_rows elif fail_if_row_invalid: redshift_options['max_error'] = 0 else: # max allowed value redshift_options['max_error'] = 100000 if hints.record_terminator is not None and \ hints.record_terminator != "\n": cant_handle_hint(fail_if_cant_handle_hint, 'record-terminator', hints) quiet_remove(unhandled_hints, 'record-terminator') if hints.header_row: redshift_options['ignore_header'] = 1 else: redshift_options['ignore_header'] = 0 quiet_remove(unhandled_hints, 'header-row') return redshift_options
def pandas_to_csv_options( records_format: DelimitedRecordsFormat, unhandled_hints: Set[str], processing_instructions: ProcessingInstructions) -> Dict[str, object]: # https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_csv.html hints = records_format.\ validate(fail_if_cant_handle_hint=processing_instructions.fail_if_cant_handle_hint) fail_if_cant_handle_hint = processing_instructions.fail_if_cant_handle_hint pandas_options: Dict[str, object] = {} pandas_options['encoding'] = hints.encoding quiet_remove(unhandled_hints, 'encoding') if hints.compression is None: # hints['compression']=None will output an uncompressed csv, # which is the pandas default. pass elif hints.compression == 'GZIP': pandas_options['compression'] = 'gzip' elif hints.compression == 'BZIP': pandas_options['compression'] = 'bz2' else: cant_handle_hint(fail_if_cant_handle_hint, 'compression', hints) quiet_remove(unhandled_hints, 'compression') if hints.quoting is None: pandas_options['quoting'] = csv.QUOTE_NONE elif hints.quoting == 'all': pandas_options['quoting'] = csv.QUOTE_ALL elif hints.quoting == 'minimal': pandas_options['quoting'] = csv.QUOTE_MINIMAL elif hints.quoting == 'nonnumeric': pandas_options['quoting'] = csv.QUOTE_NONNUMERIC else: _assert_never(hints.quoting) quiet_remove(unhandled_hints, 'quoting') pandas_options['doublequote'] = hints.doublequote quiet_remove(unhandled_hints, 'doublequote') pandas_options['quotechar'] = hints.quotechar quiet_remove(unhandled_hints, 'quotechar') if hints.escape is None: pass else: pandas_options['escapechar'] = hints.escape quiet_remove(unhandled_hints, 'escape') pandas_options['header'] = hints.header_row quiet_remove(unhandled_hints, 'header-row') if hints.dateformat is None: if hints.datetimeformattz == hints.datetimeformat: # BigQuery requires that timezone offsets have a colon; # Python (and thus Pandas) doesn't support adding the # colon with strftime. However, we can specify things # without a timezone delimiter just fine. # # Unfortunately Python/Pandas will drop the timezone info # instead of converting the timestamp to UTC. This # corrupts the time, as BigQuery assumes what it gets in # is UTC format. Boo. # # $ python3 # >>> import pytz # >>> us_eastern = pytz.timezone('US/Eastern') # >>> import datetime # >>> us_eastern.localize(datetime.datetime(2000, 1, 2, 12, 34, 56, 789012)) # .strftime('%Y-%m-%d %H:%M:%S.%f') # '2000-01-02 12:34:56.789012' # >>> # # https://github.com/bluelabsio/records-mover/issues/95 pandas_options['date_format'] = '%Y-%m-%d %H:%M:%S.%f' else: pandas_options['date_format'] = '%Y-%m-%d %H:%M:%S.%f%z' elif hints.dateformat == 'YYYY-MM-DD': if hints.datetimeformattz == hints.datetimeformat: pandas_options['date_format'] = '%Y-%m-%d %H:%M:%S.%f' else: pandas_options['date_format'] = '%Y-%m-%d %H:%M:%S.%f%z' elif hints.dateformat == 'MM-DD-YYYY': if hints.datetimeformattz == hints.datetimeformat: pandas_options['date_format'] = '%m-%d-%Y %H:%M:%S.%f' else: pandas_options['date_format'] = '%m-%d-%Y %H:%M:%S.%f%z' elif hints.dateformat == 'DD-MM-YYYY': if hints.datetimeformattz == hints.datetimeformat: pandas_options['date_format'] = '%d-%m-%Y %H:%M:%S.%f' else: pandas_options['date_format'] = '%d-%m-%Y %H:%M:%S.%f%z' elif hints.dateformat == 'MM/DD/YY': if hints.datetimeformattz == hints.datetimeformat: pandas_options['date_format'] = '%m/%d/%y %H:%M:%S.%f' else: pandas_options['date_format'] = '%m/%d/%y %H:%M:%S.%f%z' else: cant_handle_hint(fail_if_cant_handle_hint, 'dateformat', hints) quiet_remove(unhandled_hints, 'dateformat') # pandas can't seem to export a date and time together :( # # might be nice someday to only emit the errors if the actual data # being moved is affected by whatever limitation... if (hints.datetimeformattz not in (f"{hints.dateformat} HH24:MI:SSOF", f"{hints.dateformat} HH:MI:SSOF", f"{hints.dateformat} HH24:MI:SS", f"{hints.dateformat} HH:MI:SS", f"{hints.dateformat} HH:MIOF", f"{hints.dateformat} HH:MI", f"{hints.dateformat} HH24:MIOF", f"{hints.dateformat} HH24:MI")): cant_handle_hint(fail_if_cant_handle_hint, 'datetimeformattz', hints) quiet_remove(unhandled_hints, 'datetimeformattz') valid_datetimeformat = [ f"{hints.dateformat} HH24:MI:SS", f"{hints.dateformat} HH:MI:SS", f"{hints.dateformat} HH24:MI", f"{hints.dateformat} HH:MI", ] if (hints.datetimeformat not in valid_datetimeformat): cant_handle_hint(fail_if_cant_handle_hint, 'datetimeformat', hints) quiet_remove(unhandled_hints, 'datetimeformat') if hints.timeonlyformat != 'HH24:MI:SS': cant_handle_hint(fail_if_cant_handle_hint, 'timeonlyformat', hints) quiet_remove(unhandled_hints, 'timeonlyformat') pandas_options['sep'] = hints.field_delimiter quiet_remove(unhandled_hints, 'field-delimiter') pandas_options['line_terminator'] = hints.record_terminator quiet_remove(unhandled_hints, 'record-terminator') return pandas_options
def pandas_to_csv_options(records_format: DelimitedRecordsFormat, unhandled_hints: Set[str], processing_instructions: ProcessingInstructions) -> Dict[str, object]: # https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_csv.html hints = records_format.\ validate(fail_if_cant_handle_hint=processing_instructions.fail_if_cant_handle_hint) fail_if_cant_handle_hint = processing_instructions.fail_if_cant_handle_hint pandas_options: Dict[str, object] = {} pandas_options['encoding'] = hints.encoding quiet_remove(unhandled_hints, 'encoding') if hints.compression is None: # hints['compression']=None will output an uncompressed csv, # which is the pandas default. pass elif hints.compression == 'GZIP': pandas_options['compression'] = 'gzip' elif hints.compression == 'BZIP': pandas_options['compression'] = 'bz2' else: cant_handle_hint(fail_if_cant_handle_hint, 'compression', hints) quiet_remove(unhandled_hints, 'compression') if hints.quoting is None: pandas_options['quoting'] = csv.QUOTE_NONE elif hints.quoting == 'all': pandas_options['quoting'] = csv.QUOTE_ALL elif hints.quoting == 'minimal': pandas_options['quoting'] = csv.QUOTE_MINIMAL elif hints.quoting == 'nonnumeric': pandas_options['quoting'] = csv.QUOTE_NONNUMERIC else: _assert_never(hints.quoting) quiet_remove(unhandled_hints, 'quoting') pandas_options['doublequote'] = hints.doublequote quiet_remove(unhandled_hints, 'doublequote') pandas_options['quotechar'] = hints.quotechar quiet_remove(unhandled_hints, 'quotechar') if hints.escape is None: pass else: pandas_options['escapechar'] = hints.escape quiet_remove(unhandled_hints, 'escape') pandas_options['header'] = hints.header_row quiet_remove(unhandled_hints, 'header-row') # Note the limitation on Pandas export with BigQuery around # datetimeformattz: # # https://github.com/bluelabsio/records-mover/issues/95 # Pandas only gives us one parameter to set for formatting of its # Timestamp values, so we need the datetimeformat and # datetimeformattz hints to be nearly identical, modulo the # timezone at the end which will only appear if it is set in the # source data anyway: canonical_datetimeformattz = hints.datetimeformattz.replace('HH24', 'HH') canonical_datetimeformat = hints.datetimeformat.replace('HH24', 'HH') equivalent_with_timezone = f"{canonical_datetimeformat}OF" if (canonical_datetimeformattz not in [canonical_datetimeformat, equivalent_with_timezone]): cant_handle_hint(fail_if_cant_handle_hint, 'datetimeformat', hints) if 'AM' in hints.datetimeformattz: hour_specifier = '%I' else: hour_specifier = '%H' pandas_options['date_format'] = hints.datetimeformattz\ .replace('YYYY', '%Y')\ .replace('YY', '%y')\ .replace('MM', '%m')\ .replace('DD', '%d')\ .replace('HH24', '%H')\ .replace('HH12', '%I')\ .replace('HH', hour_specifier)\ .replace('MI', '%M')\ .replace('SS', '%S.%f')\ .replace('OF', '%z')\ .replace('AM', '%p') quiet_remove(unhandled_hints, 'datetimeformat') quiet_remove(unhandled_hints, 'datetimeformattz') # timeonlyformat and dateformat are handled in prep_for_csv.py and # raw times and dates never appear in dataframes passed a # .to_csv() call. quiet_remove(unhandled_hints, 'timeonlyformat') quiet_remove(unhandled_hints, 'dateformat') pandas_options['sep'] = hints.field_delimiter quiet_remove(unhandled_hints, 'field-delimiter') pandas_options['line_terminator'] = hints.record_terminator quiet_remove(unhandled_hints, 'record-terminator') return pandas_options