https://linen.dev logo
Join Slack
Powered by
Is it possible to force a tap to load an unsupport...
# troubleshooting
m
Is it possible to force a tap to load an unsupported type? For example, I am trying to load an entity named OID using tap-mssql which is of type binary. For my purposes, treating this as a varchar would be fine. I have attempted to override the schema in the meltano.yml like so:
Copy code
schema:
  'dbo-Person':
    OID:
      type: ["null", "string"]
      inclusion: available
However, the log shows 
WARNING Columns {'OID'} were selected but are not supported. Skipping them.
, and indeed when I check the output from invoking the tap I still don't see the OID field in the schema.
Moved the catalog output to a threaded message to not blow up the chat:
Copy code
{
      "tap_stream_id": "dbo-Person",
      "table_name": "Person",
      "schema": {
        "properties": {
          "OID": {
            "type": [
              "null",
              "string"
            ],
            "inclusion": "available"
          },
      "metadata": [
        { ...
        {
          "breadcrumb": [
            "properties",
            "OID"
          ],
          "metadata": {
            "selected-by-default": false,
            "sql-datatype": "binary",
            "selected": true
          }
        },
I think I need to override the sql-datatype properties using the https://docs.meltano.com/concepts/plugins#metadata-extra, but I'm not sure how the syntax would work for altering a specific entity.
b
You might be running into this issue. https://github.com/meltano/sdk/issues/1255
My best guess on the command for the metadata would be: 
meltano --environment=<env you want> config tap-mssql set _meadata dbo-person.OID sql_datatype varchar
I tried it on a column in a test project and the command completed and it was added to the 
meltano.yml
.
Copy code
- name: dev
  config:
    plugins:
      extractors:
      - name: tap-mssql
        metadata:
          dbo-testdata.testcolumn1:
            sql_datatype: integer
m
Thanks @BuzzCutNorman, I'll give that a try now. I see a warning when I set it, but let's see where it goes.
Copy code
/venv/lib/python3.9/site-packages/meltano/core/settings_service.py:475: RuntimeWarning: Unknown setting '_metadata.dbo-person.OID.sql_datatype'
  warnings.warn(f"Unknown setting {name!r}", RuntimeWarning)
No bueno, it still shows as binary in the catalog dump, and when invoking the tap I get:
Copy code
WARNING Columns {'OID'} were selected but are not supported. Skipping them.
It seems like elements would be another layer deep in the metadata extra, but I don't know what that level would be called.
As a bail out, it might make sense just to use a catalog file instead of through meltano configuration
b
I don't know either to be honest. Definitely give the catalog a try. Please let me know if that does or doesn't fix the issue. Is this production or a test project?
m
This is a test project, we haven't put meltano into production yet
b
Ok then I don't feel bad mentioning this. You can give my tap-mssql variant a try. If it runs in your environment I can at least help troubleshoot the data type.
m
Thanks, I may give that a try and let you know where I get. I appreciate your help
s
Copy code
Hi, I think that the intention is to support binary. It has logic to do this - the output is in a hex form.

<https://github.com/wintersrd/pipelinewise-tap-mssql/blob/e61e8687a552c8a6c67946b1c90f44745da90cb9/tap_mssql/sync_strategies/common.py#L123-L132>

I think however there needs to be an addition test inserted here <https://github.com/wintersrd/pipelinewise-tap-mssql/blob/e61e8687a552c8a6c67946b1c90f44745da90cb9/tap_mssql/__init__.py#L142> to support the binary column.

It wouldn't be too hard to add a PR to add support for the binary datatype. This would mean that tap-mssql would support the output of binary data as well.
m
Hey @BuzzCutNorman, I had some time to try out your variant, but I'm having an issue with a hostname based connection. With pyodbc, sqlalchemy requires that the driver keyword is specified (for me, that's driver=ODBC+Driver+18+for+SQL+Server). Am I missing somewhere that I should be passing this into the config for tap-mssql? https://docs.sqlalchemy.org/en/20/dialects/mssql.html#hostname-connections
b
Copy code
config:
      dialect: mssql
      driver_type: pyodbc
      host: <yourserver>
      port: 1433
      user: <username>
      database: <database>
      sqlalchemy_eng_params:
        fast_executemany: 'True'
      sqlalchemy_url_query:
        driver: ODBC Driver 18 for SQL Server
        TrustServerCertificate: yes
I really like using 
set --interactive
when setting up tap and targets. 
meltano config tap-mssql set --interactive
m
Oh, I do too. This was a case of me just being blind to option 17 when I originally set it up
t
Hi @michael_horvath I am trying to copy a table from mysql to snowflake and one of the column id having blob data type. I am also facing a similar issue. 
WARNING Columns {'my_column'} were selected but are not supported. Skipping them.
Is there a way that we can handle it in extractor
Copy code
schema:
  'my_table_name':
    my_column:
      type: ["null", "string"]
      inclusion: available
Open in Slack
PreviousNext
Powered by