Hi everyone I m currently trying to batch import some data i

Question

Hi everyone I m currently trying to batch import some data into a Pinot Offline table and currently running into some issues My current Pinot version is 0 7 0 currently in a docker container I have successfully added an `offline table config json` and a `schema json` file to Pinot however creating a segment doesn t appear to be working A `SEGMENT NAME tar gz` file isn t being created My current docker job spec yml looks like this ``` docker job spec yml executionFrameworkSpec name standalone segmentGenerationJobRunnerClassName org apache pinot plugin ingestion batch standalone SegmentGenerationJobRunner segmentTarPushJobRunnerClassName org apache pinot plugin ingestion batch standalone SegmentTarPushJobRunner segmentUriPushJobRunnerClassName org apache pinot plugin ingestion batch standalone SegmentUriPushJobRunner jobType SegmentCreationAndTarPush inputDirURI tmp pinot manual test rawdata 100k includeFileNamePattern glob csv outputDirURI tmp pinot manual test segments 100k overwriteOutput true pinotFSSpecs scheme file className org apache pinot spi filesystem LocalPinotFS recordReaderSpec dataFormat csv className org apache pinot plugin inputformat csv CSVRecordReader configClassName org apache pinot plugin inputformat csv CSVRecordReaderConfig tableSpec tableName rows 100k schemaURI tableConfigURI pinotClusterSpecs controllerURI ``` Some of the error messages I m getting are ```Failed to generate Pinot segment for file file tmp pinot manual test rawdata 100k rows 100k csv Caught exception while gathering stats java lang NumberFormatException For input string 5842432235322161941 at java lang NumberFormatException forInputString NumberFormatException java 65 ~ 1 8 0 282 at java lang Integer parseInt Integer java 583 ~ 1 8 0 282 ``` Any leads on this would be appreciated Thanks

Neha Pawar · Answer

this looks like a mismatch in dataTypes between the Pinot schema and the actual data

Neha Pawar · Answer

can you share the Pinot schema and some sample rows

Ken Krugler · Answer

Isn t 5842432235322161941 too big for an int type I think your schema would need to use a long

Xiang Fu · Answer

yes it s long not int glad it s still smaller than LONG MAX

Xiang Fu · Answer

otherwise maybe only double

Kha · Answer

Yes that was the issue However this brings up another issue with my DateTime I have a date value in milliseconds Epoch and it seems to not be able to read it Example date in my CSV is ```1096429682806``` Schema for date is ``` schema json dateTimeFieldSpecs name date dataType LONG format 1 MILLISECONDS EPOCH granularity 1 MILLISECONDS ``` Table config is ``` segmentsConfig timeColumnName date timeType MILLISECONDS segmentPushType APPEND segmentAssignmentStrategy BalanceNumSegmentAssignmentStrategy schemaName row1 replication 1 ``` Error in the image attached

Xiang Fu · Answer

from the value it s secondsSinceEpoch

Xiang Fu · Answer

1612471718

Xiang Fu · Answer

`1096429682806` lt this value is in the year 2004

Kha · Answer

yes that value for 2004 is in my CSV

Xiang Fu · Answer

I don t see any problem with this so far but seems that this column time is set to be seconds instead of milliseconds < Neha Pawar> anything else to check

Neha Pawar · Answer

`1096429682806` is the value for 2004 rt the error says Pinot found `1612471718` which is 1970

Neha Pawar · Answer

is that value expected 1612471718

Kha · Answer

from what I know I don t have any values that match with 1612471718

Neha Pawar · Answer

is it possible to share your input file with us we can try to reproduce

Xiang Fu · Answer

maybe 10 rows and your table config schema

Kha · Answer

Here is my direct CSV

Neha Pawar · Answer

and you entire schema table config too

Neha Pawar · Answer

not able to reproduce with your table cnfig and schema I can generate the segment just fine

Neha Pawar · Answer

only possible issue i see in your configs is this ```tableName rows 100k schemaURI tableConfigURI ```

Neha Pawar · Answer

the schema is row1 but this says rows 100k

Neha Pawar · Answer

could it be referring to some old schema

Kha · Answer

should the rows 100 that s not tableName reference the scehma

Neha Pawar · Answer

ah i see

Kha · Answer

i m not entirely sure the documentation for the batch impot example uses the same value for `tableName` and `schemaName`

Xiang Fu · Answer

It s using ` ```schemaURI <http pinot controller test 9000 tables rows 100k schema> ```

Xiang Fu · Answer

can you check what s the response for ```schemaURI tableConfigURI ```

Kha · Answer

Receiving the same error message for above

Kha · Answer

< Neha Pawar> I can confirm that changing `rows 100k` to `row1` breaks it further

Xiang Fu · Answer

< Neha Pawar> can you share the schema and table config

Xiang Fu · Answer

and Kha can use it to create the table

Neha Pawar · Answer

i just used what he shared only thing diff is the batch job spec

Xiang Fu · Answer

which pinot image are you using is it `apachepinot pinot latest`

Kha · Answer

Yes m using the latest version of pinot 0 7 0

Xiang Fu · Answer

< Neha Pawar> I think we need to add timeType into table config

Xiang Fu · Answer

``` tableName foo tableType OFFLINE segmentsConfig timeColumnName date timeType MILLISECONDS replication 1 tenants tableIndexConfig loadMode HEAP invertedIndexColumns id hash one metadata customConfigs ```

Xiang Fu · Answer

also do you have logs for batch ingestion job < Kha>

Kha · Answer

where would the logs be found in the docker instance

Neha Pawar · Answer

so strange i m also able to take your exact configs including the yml and upload

Neha Pawar · Answer

i m on the latest master that could be the only difference

Xiang Fu · Answer

`docker log lt docker container id gt `

Kha · Answer

To clarify Neha did you take my files run them and are able to successfully upload it

Kha · Answer

As of now I m going to try to remove the time column and replace it with another time format I will come back to this on Monday Thank you guys so much for your help < Neha Pawar> < Xiang Fu>

Xiang Fu · Answer

sure please let us know

Neha Pawar · Answer

yes took exactly your files I m not running on docker so i changed the `s pinot controller test localhost` and was able to upload

Neha Pawar · Answer

lets look at logs on Monday

Xiang Fu · Answer

I ve tried with docker setup and there is no issue on my side Here are my steps 1 Start pinot quickstart with docker ```docker run network=pinot demo name pinot quickstart p 9000 9000 d apachepinot pinot latest QuickStart type batch``` 2 Create Table ```docker run rm ti network=pinot demo v tmp pinot quick start tmp pinot quick start name pinot batch table creation apachepinot pinot latest AddTable schemaFile tmp pinot quick start foo schema json tableConfigFile tmp pinot quick start foo table offline json controllerHost pinot quickstart controllerPort 9000 exec``` 3 Start Ingestion job ```docker run rm ti network=pinot demo v tmp pinot quick start tmp pinot quick start name pinot data ingestion job apachepinot pinot latest LaunchDataIngestionJob jobSpecFile tmp pinot quick start docker job spec 100k yml```

Xiang Fu · Answer

I put table conf and schema on my local directory and mount to docker

Neha Pawar · Answer

i suspect there s some stray data in the input folder for you Kha

Xiang Fu · Answer

and this is the updated docker job spec file ``` cat tmp pinot quick start docker job spec 100k yml executionFrameworkSpec name standalone segmentGenerationJobRunnerClassName org apache pinot plugin ingestion batch standalone SegmentGenerationJobRunner segmentTarPushJobRunnerClassName org apache pinot plugin ingestion batch standalone SegmentTarPushJobRunner segmentUriPushJobRunnerClassName org apache pinot plugin ingestion batch standalone SegmentUriPushJobRunner jobType SegmentCreationAndTarPush inputDirURI tmp pinot quick start rawdata includeFileNamePattern glob csv outputDirURI tmp pinot manual test segments overwriteOutput true pinotFSSpecs scheme file className org apache pinot spi filesystem LocalPinotFS recordReaderSpec dataFormat csv className org apache pinot plugin inputformat csv CSVRecordReader configClassName org apache pinot plugin inputformat csv CSVRecordReaderConfig tableSpec tableName foo schemaURI tableConfigURI pinotClusterSpecs controllerURI ```

Xiang Fu · Answer

Kha I feel you can delete the table and corresponding schema and retry

Kha · Answer

Just to update on this I restarted my Pinot cluster on docker tested the insert after and it showed the same error as above then I changed my time format to seconds which the insert was a success and changed the time format back to milliseconds where the insert was now successful I then noticed that the query values in the Pinot UI didn t match my CSVs it was referencing an old CSV that was deleted a while ago I restarted docker regenerated the CSVs and it s now working Thanks for your help < Neha Pawar> < Xiang Fu> Please consider this issue closed white check mark