https://linen.dev logo
Join Slack
Powered by
Hi, does anyone use bigquery as loader/target? the...
# announcements
n
Hi, does anyone use bigquery as loader/target? there is no "out of box" support for it in meltano yet and i am not sure which singer target for bigquery is good to use. so if anyone has any experience with bigquery target, any pointer in right direction is appreciated.
d
I know that a few people in this Slack workspace have had good results with https://github.com/RealSelf/target-bigquery, and there's also https://github.com/transferwise/pipelinewise-target-bigquery. In general, the PipelineWise taps are very good, and OOTB support is in the works: https://gitlab.com/meltano/meltano/-/issues/2357
n
Thanks, RealSelf one was last updated long time ago and i think there are lots of changes in bigquery since then. Also I have had some experience with pipelinewise version (while it was in development), so i think i will go with that for time being.
d
Sounds good, please let me know how it goes!
n
🙂 seems like this is going to be fun work, i am getting `Executable 'pipelinewise-target-bigquery' could not be found. Loader 'pipelinewise-target-bigquery' may not have been installed yet using 
meltano install loader pipelinewise-target-bigquery
, or the executable name may be incorrect.`
d
The executable name is 
target-bigquery
, not 
pipelinewise-target-bigquery
, so you'll just have to change that in 
meltano.yml
🙂
I've created an issue to add one or more of the target-bigquery loaders, by the way: https://gitlab.com/meltano/meltano/-/issues/2374
n
Awesome changing executable name works 🙂, i did try with changing loader name, but did not realise i should/can change executable name as well 🙂
z
Hi, @nil! Did it work with you to actually load the data into bigquery? Because I receive an error with the authentication of BigQuery
n
Hi @zlatan, I have not gone that far yet 🙂, (may be i am doing too many things at once) just configured the project so far, will update status here once i have more info.
z
Okay, great!
n
@zlatan Yes i can confirm i see same problems, so far i have tried to set GOOGLE_APPLICATION_CREDENTIALS in two ways 1) .env file in project root 2) adding it into meltano.yml config but neither works @douwe_maan any pointers? 😁
d
@nil Did you try this workaround? https://meltano.slack.com/archives/CFG3C3C66/p1602007097102300?thread_ts=1601816553.046800&cid=CFG3C3C66 If that's what you're referring to with point 2, can you share your 
meltano.yml
definition, and any error messages you're seeing from target-bigquery?
n
With point 1 the error message is:
Copy code
meltano         | Loading failed (1): google.auth.exceptions.DefaultCredentialsError: Could not automatically determine credentials. Please set GOOGLE_APPLICATION_CREDENTIALS or explicitly create credentials and re-run the application. For more information, please see <https://cloud.google.com/docs/authentication/getting-started>
and the .env is:
Copy code
export GOOGLE_APPLICATION_CREDENTIALS=./cred.json
With point 2, the loader definition is:
Copy code
loaders:
  - name: target-bigquery
    namespace: bigquery
    pip_url: pipelinewise-target-bigquery
    executable: target-bigquery
    settings:
    - name: project_id
    - name: dataset_id
    config:
      project_id: tester
      dataset_id: re_test
      GOOGLE_APPLICATION_CREDENTIALS: cred.json
      default_target_schema: re_test
where cred.json is the bigquery credentials-json-file (inside project root folder)
d
@nil All right, can you add the setting like @zlatan did in https://meltano.slack.com/archives/CFG3C3C66/p1602007521103200?thread_ts=1601816553.046800&amp;cid=CFG3C3C66, and use 
credentials_path
under 
config
rather than 
GOOGLE_APPLICATION_CREDENTIALS
?
n
credentials_path workaround works 🙂 , but there are other errors now, i am trying to clean the output so i can share it here. The working version of loader (with workaround and cred.json in project root):
Copy code
loaders:
  - name: target-bigquery
    namespace: bigquery
    pip_url: pipelinewise-target-bigquery
    executable: target-bigquery
    settings:
    - name: project_id
    - name: dataset_id
    - name: credentials_path
      env: GOOGLE_APPLICATION_CREDENTIALS
    config:
      project_id: tester
      dataset_id: re_test
      default_target_schema: re_test
      credentials_path: ./cred.json
@douwe_maan ok so the error i was getting was
Copy code
target-bigquery | google.api_core.exceptions.BadRequest: 400 Query error: Value of type ARRAY<STRUCT<name STRING, value STRING>> cannot be assigned to properties, which has type ARRAY<STRING> at [5:891]
is something to do with pipelinewise-target-bigquery (so as far as meltano is concerned all works i think, from tap-recharge i can see record were fetched so that is working as it should 100%) I think the problem is that the schema is not correctly created for bq, the correct schema/field is:
Copy code
FIELD                    TYPE     MODE 
==========================================
properties	            RECORD	REPEATED	
properties.value	    RECORD	NULLABLE	
properties.value.name 	STRING	NULLABLE	
properties.value.value	STRING	NULLABLE
where as the one that is created is, hence it fails when it comes to loading of the the actual records/values/data into the table
Copy code
FIELD                    TYPE     MODE 
==========================================
properties	            STRING	REPEATED
If you still want i can provide full log of this error if it helps. I think i might have to look at other target-bigquery as well :)
d
Yeah that's not a Meltano issue anymore. Could it be that your tap is outputting 
SCHEMA
messages that don't match the actual schema of the 
RECORD
messages output later?
Depending on which tap you're using, you may be able to fix this using https://meltano.com/docs/plugins.html#schema-extra
n
i will do bit more reading, also i was able to run this combination successfully at some point using pipelinewise (tap-recharge and pipelinewise-target-bigquery), but i think some things has changed since then, i will try to look at schema output of tap-recharge and also see if other bq target works (while also reading more documentation of meltano 🙂). Thank you for all you help so far, it has made on-boarding process a breeze 👍
d
Sounds good, glad I could be of help!
6 Views
Open in Slack
PreviousNext
Powered by