Hello, I am trying to load a csv (generated from Redshift UNLOAD) command and the dates are in this format (in schema file):

"name": "created_date",
            "dataType": "STRING",
            "format" : "1:MILLISECONDS:SIMPLE_DATE_FORMAT:YYYY-MM-dd HH24:MI:<http://SS.MS|SS.MS>",
            "granularity": "1:MILLISECONDS"

example from csv file: 2020-03-01 073108.792457. I keep on getting failed to generate pinot segment and java illegal argument exception error

java.lang.IllegalArgumentException: Illegal pattern component: I
	at org.joda.time.format.DateTimeFormat.parsePatternTo(DateTimeFormat.java:566) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.joda.time.format.DateTimeFormat.createFormatterForPattern(DateTimeFormat.java:687) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.joda.time.format.DateTimeFormat.forPattern(DateTimeFormat.java:177) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.apache.pinot.spi.data.DateTimeFormatPatternSpec.<init>(DateTimeFormatPatternSpec.java:57) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.apache.pinot.spi.data.DateTimeFormatSpec.<init>(DateTimeFormatSpec.java:59) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.apache.pinot.core.indexsegment.generator.SegmentGeneratorConfig.setTime(SegmentGeneratorConfig.java:212) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.apache.pinot.core.indexsegment.generator.SegmentGeneratorConfig.<init>(SegmentGeneratorConfig.java:138) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.apache.pinot.plugin.ingestion.batch.common.SegmentGenerationTaskRunner.run(SegmentGenerationTaskRunner.java:95) ~[pinot-all-0.7.1-jar-with-dependencies.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]
	at org.apache.pinot.plugin.ingestion.batch.standalone.SegmentGenerationJobRunner.lambda$run$0(SegmentGenerationJobRunner.java:199) ~[pinot-batch-ingestion-standalone-0.7.1-shaded.jar:0.7.1-afa4b252ab1c424ddd6c859bb305b2aa342b66ed]

Has anyone run into this issue? Do I need to convert the dates to EPOCH when generating CSV?

what’s your created_date format?

In Redshift table it's stored a timestamp without time zone

This is not a valid simple date format. Can you please try

yyyy-MM-dd HH:mm:ss.SSS

?

Thanks Jackie, let me try this format instead.

Seems microseconds are not supported in simple date format. Please double check the value parsed with this format

That worked! Moving on to next error. I exported file without header and it's giving me "header contains duplicate name error"

If you don't have the header within the file, you need to provide a

CSVRecordReaderConfig

which contains the header info

I was actually just going to regenerate the file with header.

@Kamal Chavda Can you please try wrapping the string value within double quotes? Not 100% sure if pinot CSV reader can handle that though

Hi @Jackie, I tried doing that by adding quotes but got an error. It didn't like quotes around the delimiters.

@Kamal Chavda I did some experiment and Pinot CSV record reader can handle the escaped delimiter with double quotes. One finding is that you cannot have space after the double quote

Thank @Jackie! Redshift UNOAD actually adds double quotes automatically to columns with delimiter (',') (ex: in a CSV file. I tested out by exporting a record and load it to Pinot. One strange thing I'm noticing is I'm getting error

java.lang.IllegalStateException: Cannot read single-value from Object[]: [check 175, Some Name] for column: reference_id

But when I check the actual file for that value it's "check 175; Some Name". Not sure how that's happening.

I see what is going on here.

;

is preserved as the multi-value delimiter. E.g.

a;b

will be interpreted as

[a, b]

. One work-around would be picking an unused character is the multi-value delimiter if you don't expect multi-value within the csv file

you can also configure the delimiter in your csvreaderconfig

Thanks guys. That's what I was actually going to ask. So I can update this line to change the multi_value or line 28 for the delimiter.

The problem is that the column is not defined as multi-value column, and loading single value from an array throws the exception

Ah, okay got it. Didn't realize that. It's not mentioned on the dimensionfieldspec nvm it's the singleValueField boolean.

you can follow this : https://docs.pinot.apache.org/basics/data-import/pinot-input-formats#csv

Thank you Xiang and Jackie for all your help! I used your advice and addressed the ';' value in columns during my csv generation. The processing is finally running! 🙂

What the data look like?

In the csv file I've removed the microseconds from the timestamp so

2020-03-01 07:31:08

ic, then you can add more columns for your time, also recently @Jackie added a timestamp type, that might help as well

You can have multiple time fields and use ingestion transform to generate them: https://docs.pinot.apache.org/developers/advanced/ingestion-level-transformations

I was trying to do

select...from table where year(create_date) >= 2020

. What you posted above does work though so will modify query. Thanks for sharing the ingestion transform link, will use it to transform columns!

If you ingest the data as data type

TIMESTAMP

,

year()

will work

Thanks @Jackie, I totally missed your response yesterday. So you're suggesting using the ingestion-level-transformations to load the source timestamp as timestamp in Pinot destination table? Xiang Fu mentioned there is a timestamp type? Instead of SIMPLE_DATE_FORMAT I can use TIMESTAMP?

Yes, and the source format is already valid timestamp, and you don’t need to do extra transform

Thanks Jackie. Yes, I'm on 0.7.1 😞

TIMESTAMP is always in millis, so

1:MILLISECONDS:TIMESTAMP