INFO Required : python dataproc_templates GCSTOJDBC - Input Options

[AshishKumar0705]

Hi Team ,

Could you please guide for the below Query .

we are using GCSTOJDBC Template , how to pass below arguments . I have tried adding below parameter but its not parsing or working.

Could you please let me know how we can pass header , inferschema and csv_sep as tab for GCSTOJDBC template.

https://github.com/GoogleCloudPlatform/dataproc-templates/blob/main/python/dataproc_templates/gcs/gcs_to_jdbc.py

"--gcs.jdbc.input.header",
"false",
"--gcs.jdbc.input.sep",
"\t",

we are using Airflow and dataproc template . Arguments are attached .

discussion_git.txt

[nj1973]

I've not used Airflow but I can use the template as you suggest you'd like to:

./bin/start.sh \
-- --template=GCSTOJDBC \
--gcs.jdbc.input.location="gs://my-bucket/6_test_no_header.tsv" \
--gcs.jdbc.input.format="csv" \
--gcs.jdbc.input.header="false" \
--gcs.jdbc.input.sep="\t" \
--gcs.jdbc.output.url="jdbc:oracle:thin:@${ORA_DSN}" \
--gcs.jdbc.output.driver="oracle.jdbc.driver.OracleDriver" \
--gcs.jdbc.output.table="sh.test_tsv" \
--gcs.jdbc.output.mode="overwrite"

I used Oracle rather than PostgreSQL but the parameters are accepted and the table is created:

SQL> desc sh.test_tsv
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 _c0						    NUMBER(19,4)
 _c1						    VARCHAR2(255)
 _c2						    VARCHAR2(255)
 _c3						    VARCHAR2(255)
 _c4						    VARCHAR2(255)
 _c5						    VARCHAR2(255)
 _c6						    VARCHAR2(255)
 _c7						    VARCHAR2(255)
 _c8						    VARCHAR2(255)
 _c9						    VARCHAR2(255)
 _c10						    TIMESTAMP(6)
 _c11						    NUMBER(19,4)
 _c12						    VARCHAR2(255)
 _c13						    VARCHAR2(255)
 _c14						    NUMBER(19,4)

What error are you getting?

[AshishKumar0705]

Hi nj1973,

Thanks a lot for swift response ...!!!

I am not getting error while running Airflow with GCStoJDBC template , but 1st line of the file is ingested as a single column ( header ) and rest of row into one column .

we are running with main.py not start.sh .

Could you please share the line of code where Spark option is getting added as option for header true/false and delimiter type.

Test file attached for reference .

Thanks
Sample_file_test.TWE.txt

[nj1973]

Looking at your test file it appears that you have spaces and not tabs separating your fields?

[AshishKumar0705]

Yeah Sorry for the wrong file ...!!!

As mentioned we are running with main.py .

Could you please share the line of code where Spark option is getting added for header true/false and delimiter type in Dataproc Template REPO.

Thanks
msc_Test_view_file_tab000000000000.csv

[nj1973]

The function call here is the one that ingests the data using Spark and adds options:

dataproc-templates/python/dataproc_templates/gcs/gcs_to_jdbc.py

Line 135 in 1c12bb1

input_data = ingest_dataframe_from_cloud_storage(spark, args, input_location, input_format, "gcs.jdbc.input.")

This is the line within that function that generates a dict of the options:

dataproc-templates/python/dataproc_templates/util/dataframe_reader_wrappers.py

Line 34 in 1c12bb1

spark_options = {csv_input_constant_options[k]: v

[nj1973]

I've tested with your TSV file to PostgreSQL and it imported successfully:

# \d sh.test_tab_csv
               Table "sh.test_tab_csv"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 FirstName | text    |           |          |
 LastName  | text    |           |          |
 Age       | integer |           |          |

My test command:

./bin/start.sh \
-- --template=GCSTOJDBC \
--gcs.jdbc.input.location="gs://${BUCKET}/msc_Test_view_file_tab000000000000.csv" \
--gcs.jdbc.input.format="csv" \
--gcs.jdbc.input.header="true" \
--gcs.jdbc.input.sep="\t" \
--gcs.jdbc.output.url="jdbc:postgresql://${PG_DSN}" \
--gcs.jdbc.output.driver="org.postgresql.Driver" \
--gcs.jdbc.output.table="sh.test_tab_csv" \
--gcs.jdbc.output.mode="overwrite"

[AshishKumar0705]

Thanks a lot for the above ...!!!