amazon-s3google-bigquerydts

Single Digit Day and Month in BigQuery Data Transfer Service

When using run BigQuery Data Transfer Service to ingest data from S3, I needed to use run-time parameters to provide the current month and day in the URI.

Example: s3://bucket/year=2022/month=6/day=6/*.json

I was unable to find single digit month. Looks like we have the single digit day yet the preceding space makes it not usable. Please refer here

Do we have any single-digit month and day format without any preceding space where we can use on URI path?

Solution

  • (update) I'm not sure below format string will work as same in DTS run-time parameter, but I think you can try this at least. I've added some format specifiers to remove preceding space.

    SELECT FORMAT_DATE('year=%Y/month=%_-m/day=%-e', '2022-06-06') AS uri_date;

    enter image description here

    %m seems to cover the single digit of month.

    SELECT PARSE_DATE('year=%Y/month=%m/day=%e', 'year=2022/month=6/day=6') AS uri_date;

    enter image description here