https://linen.dev logo
Join Slack
Powered by
When I run tap-quickbooks (accounting software) to...
# troubleshooting
k
When I run tap-quickbooks (accounting software) to target-postgres (the transferwise variant), I get no errors, but no tables or schemas are created in my DB. 😕 I tested tap-quickbooks to jsonl, and it successfully writes selected data to corresponding files. Anyone know why the schema has not committed to Postgres? (Copying some of the debug log in this thread-- this is all sandbox company data don’t worry)
Copy code
name=tap-quickbooks (out) run_id=654eb377-4df5-433d-8b62-08032b9012c8 stdio=stdout
2022-05-25T04:00:21.427532Z [debug    ] {"type": "RECORD", "stream": "Customer", "record": {"Taxable": false, "BillAddr": "{\"Id\": \"30\",
 \"Line1\": \"45612 Main St.\", \"City\": \"Bayshore\", \"CountrySubDivisionCode\": \"CA\", \"PostalCode\": \"94326\", \"Lat\": \"45.256574
\", \"Long\": \"-66.0943698\"}", "ShipAddr": "{\"Id\": \"30\", \"Line1\": \"45612 Main St.\", \"City\": \"Bayshore\", \"CountrySubDivisionC
ode\": \"CA\", \"PostalCode\": \"94326\", \"Lat\": \"45.256574\", \"Long\": \"-66.0943698\"}", "Job": false, "BillWithParent": false, "Bala
nce": 375.0, "BalanceWithJobs": 375.0, "CurrencyRef": "{\"value\": \"USD\", \"name\": \"United States Dollar\"}", "PreferredDeliveryMethod"
: "Print", "IsProject": false, "Id": "29", "MetaData": "{\"CreateTime\": \"2022-04-12T17:29:04-07:00\", \"LastUpdatedTime\": \"2022-04-18T1
1:09:08-07:00\"}", "FullyQualifiedName": "Weiskopf Consulting", "CompanyName": "Weiskopf Consulting", "DisplayName": "Weiskopf Consulting",

 "PrintOnCheckName": "Weiskopf Consulting", "Active": true, "PrimaryEmailAddr": "{\"Address\": \"<mailto:Consulting@intuit.com|Consulting@intuit.com>\"}"}, "version": 165
3451221227, "time_extracted": "2022-05-25T04:00:21.227588Z"} cmd_type=extractor job_id=QBToPostgres05_24 name=tap-quickbooks (out) run_id=6
54eb377-4df5-433d-8b62-08032b9012c8 stdio=stdout
2022-05-25T04:00:21.428100Z [debug    ] {"type": "ACTIVATE_VERSION", "stream": "Customer", "version": 1653451221227} cmd_type=extractor job
_id=QBToPostgres05_24 name=tap-quickbooks (out) run_id=654eb377-4df5-433d-8b62-08032b9012c8 stdio=stdout
2022-05-25T04:00:21.428605Z [debug    ] {"type": "STATE", "value": {"current_stream": "Customer", "bookmarks": {"Invoice": {"version": null
}, "Customer": {"version": null}}}} cmd_type=extractor job_id=QBToPostgres05_24 name=tap-quickbooks (out) run_id=654eb377-4df5-433d-8b62-08
032b9012c8 stdio=stdout
2022-05-25T04:00:21.429112Z [debug    ] {"type": "STATE", "value": {"current_stream": null, "bookmarks": {"Invoice": {"version": null}, "Cu
stomer": {"version": null}}}} cmd_type=extractor job_id=QBToPostgres05_24 name=tap-quickbooks (out) run_id=654eb377-4df5-433d-8b62-08032b90
12c8 stdio=stdout
Copy code
022-05-25T04:00:21.435396Z [info     ] INFO Finished sync             cmd_type=extractor job_id=QBToPostgres05_24 name=tap-quickbooks run_
id=654eb377-4df5-433d-8b62-08032b9012c8 stdio=stderr
2022-05-25T04:00:21.601896Z [info     ] time=2022-05-25 00:00:21 name=target_postgres level=INFO message=Table '"customer"' exists cmd_type
=loader job_id=QBToPostgres05_24 name=target-postgres run_id=654eb377-4df5-433d-8b62-08032b9012c8 stdio=stderr
2022-05-25T04:00:21.838147Z [info     ] time=2022-05-25 00:00:21 name=target_postgres level=INFO message=Loading 29 rows into 'quickbooks_d
ata."customer"' cmd_type=loader job_id=QBToPostgres05_24 name=target-postgres run_id=654eb377-4df5-433d-8b62-08032b9012c8 stdio=stderr
2022-05-25T04:00:21.844441Z [info     ] time=2022-05-25 00:00:21 name=target_postgres level=INFO message=Loading 31 rows into 'quickbooks_d
ata."invoice"' cmd_type=loader job_id=QBToPostgres05_24 name=target-postgres run_id=654eb377-4df5-433d-8b62-08032b9012c8 stdio=stderr

2022-05-25T04:00:22.146923Z [info     ] time=2022-05-25 00:00:22 name=target_postgres level=INFO message=Loading into quickbooks_data."cust
omer": {"inserts": 0, "updates": 29, "size_bytes": 19531} cmd_type=loader job_id=QBToPostgres05_24 name=target-postgres run_id=654eb377-4df
5-433d-8b62-08032b9012c8 stdio=stderr
2022-05-25T04:00:22.164088Z [info     ] time=2022-05-25 00:00:22 name=target_postgres level=INFO message=Loading into quickbooks_data."invo
ice": {"inserts": 0, "updates": 31, "size_bytes": 54090} cmd_type=loader job_id=QBToPostgres05_24 name=target-postgres run_id=654eb377-4df5
-433d-8b62-08032b9012c8 stdio=stderr
2022-05-25T04:00:22.184086Z [debug    ] {"current_stream": null, "bookmarks": {"Invoice": {"version": null}, "Customer": {"version": null}}
} cmd_type=loader job_id=QBToPostgres05_24 name=target-postgres (out) run_id=654eb377-4df5-433d-8b62-08032b9012c8 stdio=stdout
2022-05-25T04:00:22.193260Z [info     ] Incremental state has been updated at 2022-05-25 04:00:22.193056.
2022-05-25T04:00:22.193836Z [debug    ] Incremental state: {'current_stream': None, 'bookmarks': {'Invoice': {'version': None}, 'Customer':
 {'version': None}}}
kc-meltano.yaml
kc-meltano.yaml
v
Copy code
meltano --log-level=debug elt tap-quickbooks target-postgres --job_id=QBToPostgres05_25
@kathryn_cowie run command from Office hours!
In your case Kathryn I'd try 
meltano invoke tap-quickbooks > out
Then 
cat out | meltano invoke target-postgres
Or you could even pull in a different tap for the output data
a
Also, as noted from office hours, would be good to double check default schema is set as a loader setting value, and would be good to double check that the job ID having already been run with 
target-csv
is not preventing new data from flowing because it thinks that data has already been received.
If you want to use 
meltano run
instead of 
meltano elt
, then job IDs are generated for you automatically and the target-csv and target-postgres would just automatically use different IDs.
Copy code
meltano --log-level=debug run tap-quickbooks target-csv
meltano --log-level=debug run tap-quickbooks target-jsonl
meltano --log-level=debug run tap-quickbooks target-postgres
These would all use different job IDs automatically 👆
k
Update: I added the default schema 
meltano config target-postgres set default_target_schema quickbooks_data
, removed the JOB id and ran the above cmmd, and also tried 
cat out | meltano invoke target-postgres
. Good news: now I see my schema quickbooks_data
Copy code
operations=> SELECT schema_name FROM information_schema.schemata;
    schema_name
--------------------

 pg_catalog
 information_schema
 public
 quickbooks_data
(4 rows)
Good news for people who like bad news: Still no data tables created
passing along comments from my coworker:
looks like it generates a transaction in the database, but never commits it. pretty much the only way that could happen is if they’re rolling back a txn (intentionally or due to some other silent failure)
v
Getting somewhere it seems • Ok so in the quickbooks_data schema there's no tables? • cat out has data inside of it? • What OS are you running on (shouldnt' matter at all)
try this data file (just pulled from https://gitlab.com/autoidm/autoidm-target-mssql/-/blob/main/data there's tons of these in different tests) The idea here is we need to see is it the output data itself? Or is it the target?
data
There should be an error if data isn't being written properly in the target. So we want to come back to this as well as this should be very clear as to what's happening Commits normally happen when the tap is doing writing or state messages are sent from the tap so that "should" be fine but of course there can always be something going on. Normally this ends up being something easy but we shall see
k
• correct, no tables • yes cat out has data • mac High Sierra (10.13.6)
out
out
v
You try with the file I sent I"ll whip together an env on my end and try yours
Took your meltano.yml and I"m getting. Added that to my meltano.yml (incoming) Now I have data flowing no issue
So the job failed for me with a clear error
message has been deleted
Copy code
version: 1
default_environment: dev
project_id: 14d8d05f-ddd8-4b34-81c0-5756f1c13c4f
plugins:
  extractors:
  - name: tap-quickbooks
    namespace: tap_quickbooks
    label: Quickbooks
    description: Accounting management platform
    docs: <https://hub.meltano.com/extractors/quickbooks.html>
    repo: <https://github.com/hotgluexyz/tap-quickbooks>
    pip_url: git+<https://github.com/hotgluexyz/tap-quickbooks.git>
    capabilities:
    - properties
    - discover
    - state
    settings_group_validation:
    - - client_id
      - client_secret
      - refresh_token
      - realmId
      - start_date
    settings:
    - name: realmId
      label: Realm ID
    - name: client_id
      kind: password
      label: Client ID
    - name: client_secret
      kind: password
    - name: refresh_token
      kind: password
    - name: start_date
      kind: date_iso8601
      description: Determines how much historical data will be extracted. Please be
        aware that the larger the time period and amount of data, the longer the initial
        extraction can be expected to take.
    - name: is_sandbox
      kind: boolean
      value: false
      description: Use Quickbooks Sandbox
    - name: select_fields_by_default
      kind: boolean
      value: true
      description: Select by default any new fields discovered in Quickbooks objects
    - name: state_message_threshold
      kind: integer
      value: 1000
      description: Generate a STATE message every N records
    - name: max_workers
      kind: integer
      value: 8
      label: Maximum number of threads to use
  loaders:
  - name: target-jsonl
    variant: andyh1203
    pip_url: target-jsonl
  - name: target-csv
    variant: singer-io
    pip_url: target-csv
  - name: target-postgres
    variant: transferwise
    pip_url: pipelinewise-target-postgres
environments:
- name: dev
  config:
    plugins:
      extractors:
      - name: tap-quickbooks
        config:
          realmId: redacted
          start_date: '2021-01-01T00:00:00Z'
          is_sandbox: true
          api_type: BULK
          state_message_threshold: 100
          max_workers: 8
          select_fields_by_default: false
        select:
        - Customer.Id
        - Customer.CompanyName
        - Customer.Balance
        - Customer.BillAddr
        - Customer.CompanyName
        - Invoice.CustomerRef
        load_schema: quickbooks_data
      loaders:
      - name: target-jsonl
      - name: target-csv
      - name: target-postgres
        config:
          host: localhost
          user: postgres
          password: postgres
          default_target_schema: qb
          dbname: postgres
- name: staging
- name: prod
k
thanks! I tried out the above configuration, but still struggling. Schema is created; not any tables. 😕 Wonder if it may be related to my postgres permissions, though I don’t see any associated error message. Will keep at it
Copy code
time=2022-05-27 14:58:05 name=target_postgres level=INFO message=Table '"employees"' exists

time=2022-05-27 14:58:06 name=target_postgres level=INFO message=Loading 13 rows into 'quickbooks_data."employees"'
time=2022-05-27 14:58:07 name=target_postgres level=INFO message=Loading into quickbooks_data."employees": {"inserts": 0, "u
pdates": 13, "size_bytes": 2264}
{"bookmarks": {"employees": {}}}
2022-05-27T18:58:07.732342Z [debug    ] Deleted configuration at /Users/katecowie/Documents/nested-knowledge/meltano-project
s/quickbooks-csv/.meltano/run/target-postgres/target.1aa9c326-d18d-4416-baf3-1efb0cc17416.config.json
2022-05-27T18:58:07.860869Z [debug    ] Starting new HTTPS connection (1): <http://www.google-analytics.com:443|www.google-analytics.com:443>
2022-05-27T18:58:08.053321Z [debug    ] <https://www.google-analytics.com:443> "POST /collect HTTP/1.1" 200 35
2022-05-27T18:58:08.061309Z [debug    ] Starting new HTTPS connection (1): <http://sp.meltano.com:443|sp.meltano.com:443>
2022-05-27T18:58:08.523842Z [debug    ] <https://sp.meltano.com:443> "GET /i?e=se&se_ca=meltano+invoke&se_ac=meltano+invoke+ta
rget-postgres++--environment%3Def260e9aa3c673af240d17a2660480361a8e081d1ffeca2a5ed0e3219fc18567&se_la=14d8d05f-ddd8-4b34-81c
0-5756f1c13c4f&eid=d790e7fd-02df-47c6-b74a-9b87c5960006&dtm=1653677888057&tv=py-0.10.0&p=pc&stm=1653677888000 HTTP/1.1" 302
0
v
You could try spinning up your own postgres db to rule that out
Copy code
time=2022-05-27 14:58:06 name=target_postgres level=INFO message=Loading 13 rows into 'quickbooks_data."employees"'
time=2022-05-27 14:58:07 name=target_postgres level=INFO message=Loading into quickbooks_data."employees": {"inserts": 0, "u
pdates": 13, "size_bytes": 2264}
Means data is in your database
How are you verifying whether there's data in your DB?
k
=> \dt
v
Time for a 5 min screenshare?
dbeaver
We got it! We had to select from the quickbooks_data schema
k
Thanks a ton!! I invoked the meltano dragon shortly before you called--after noticing on the help manual-- and it seems to have been a good omen
6 Views
Open in Slack
PreviousNext
Powered by