Singlestore and Spark: handling newline character

user_4652875 0
Viewed 53

I have a scenario where I'm exporting tables to S3 using SELECT INTO S3... query. This is the sample command(python) that I am using to export:

export_to_s3_sql = f"SELECT * FROM {database}.{table} " \
                           f"INTO S3 '{s3_full_path}/{database}/{table}/{table}' " \
                           f"CONFIG '{json.dumps(export_config)}' " \
                           f"CREDENTIALS '{json.dumps(export_creds)}' " \
                           f"FIELDS TERMINATED BY '\\t' ENCLOSED BY '\"' ESCAPED BY '\\\\' " \
                           f"LINES TERMINATED BY '\\r'"

Once export is complete, I read the tables using Spark. My tables are big in size(~2TB) and sometimes they contain newline characters in the column values.

Since they contain new line characters, I am forced to use multiline: true when reading the CSV via Spark. This causes Spark to read one file(~80GB) via one core with num partition equal to number of csv files.

My aim is to identify whether my CSV in S3 contains newline characters as column value or not when reading via Spark. So that I can remove multiline option from some of my spark jobs

One way to verify is to save the row counts during export. but a table might get updated during, before, or after the query execution.

  1. My first question is, what will happen if the table gets updated during the execution of the above query?
  2. During the export is there any simple way to know if any of the columns contain newline characters?
  3. Can I somehow save row count in a separate file? Given that a table may get update instructions anytime.
  4. During the export, Is there any way to replace all the newline characters in column values with empty strings?
  5. Is there any way I can add a new column with some default value? I will use this flag to detect if my CSV has new lines characters as
singlestore pyspark sql
1 Answers

Try adding a condition \\n with lines terminated by clause and also add FIELDS TERMINATED BY '\\t\\r\\n' so it can help skip empty lines and reduce extra space from the table.

        export_to_s3_sql = f"SELECT * FROM {database}.{table} " \
                           f"INTO S3 '{s3_full_path}/{database}/{table}/{table}' " \
                           f"CONFIG '{json.dumps(export_config)}' " \
                           f"CREDENTIALS '{json.dumps(export_creds)}' " \
                           f"FIELDS TERMINATED BY '\\t\\r\\n' ENCLOSED BY '\"' ESCAPED BY '\\\\' " \
                           f"LINES TERMINATED BY '\\r\\n'"
user_196281730
Related