https://linen.dev logo
Join Slack
Powered by
Hi I am new to Meltano. Have been getting a Linu...
# getting-started
p
Hi I am new to Meltano. Have been getting a Linux VM stood up at my organisation over the last few weeks in order to trial Meltano as a replication tool (with CDC) for the replication of data from our on-premise Oracle databases to our account in Snowflake. After a few issues and what seemed like an age, I finally have a Linux VM to use and Meltano installed, along with Tap-Oracle and the Meltano variant of the Snowflake loader. I have managed to get a single table with 38 rows replicated across as a full load, no CDC yet. I have however run into a few issues that I am not sure how to resolve. 1. I see that the table created in Snowflake has not preserved the original column order, but instead created the columns in alphabetical order. How do I preserve the column order of the original Oracle table. 2. An additional column named "__LOADED_AT" was added to the table, how do I stop that from happening? 3. The size of the VARCHAR columns was not preserved, it has created them all as VARCHAR(16777216), how do I preserver the original column and number sizes? I presume there is a data mapping somewhere I can adjust? 4. Even though I only had 1 table in the extractor "select:" section of meltano.yml, the job still took 7 minutes to run as it appeared to be looking and discarding the 20,000 other tables in the database. How do I stop it doing that, and only look at the list of tables I want? I suspect of have missed a step in the setup somewhere, but confess to not being able to find a nice set of instructions on how to set this up. Any help will be very much appreciated. Regards Phil
a
Hi, @phil_chapman. Sorry to hear of these challenges. We're actually working to deprecate the Meltano snowflake loader and moving to the pipelinewise (Wise) variant. Are you able to switch to that variant by chance?
Regarding your last point, the longer runtime is probably a factor of discovering and caching the initial catalog of streams. After first run, I would expect performance to speed up significantly.
p
Yes, I can definitely switch to Snowflake loader, will that address some of the issues I am having?
Hi AJ. I installed the pipelinewise variant. When configuring through the GUI, there are some extra required fields: • S3 Bucket - Have not had to supply that before for anything to do with Snowflake, did not need it for the Meltano variant. Basically I don't know what our S3 bucket is or if I even have access to it. • Stage - Do I need to pre-create my own stage for this loader? • File Format - Again why do I need to supply this and if so what should it be to accept the input from the tap-oracle extractor?
a
It should work with "internal stages" so as to not need S3 bucket and stage params. It should not be listed as required but they were previously required. I will open an issue to get those marked optional. I believe in the meanwhile, you can set them in the UI and then delete them from 
meltano.yml
.
The file_format config is looking for a file_format object on Snowflake, and the instructions to create it are documented here in their GitHub repo. (In the future, that too will be optional.)
p
Thanks AJ. I have managed to get the loader to work, however there has only been a minor improvement. 1. Columns are still in alphabetical order in Snowflake. 2. Data types in Snowflake are still not preserving the original sizes from Oracle. My runs are still taking 7 mins to run for a single small table. If I delete the table from Snowflake and rerun the pipeline, it still seems to be re-cataloging the Oracle source and individually discarding the thousands of tables & columns I don't care about.
a
Okay. I'm not sure what would cause the tap to re-run 
tap-oracle
discovery (creating the catalog) on each execution, and I am surprised to hear the column ordering is still being created alphabetically. We should be able to diagnose to column ordering, but I don't know if anything can be done about the sizing. (Snowflake compresses all strings anyway, which is why this may not have been prioritized on the target connector side.) I did research the other point from above and found, at least with the latest Meltano 1.85.0, the stage and S3 bucket should be displaying as optional now in the UI:
p
Awesome thanks for logging that AJ. The intention is for us to replicate our on-premise databases to a "data lake" in Snowflake, and then load the resulting metadata into a Data Catalog tool. Preserving the source systems metrics on field sizes and column position is important to allow for accurate representation in the Data Catalog. This may be a show stopper for us. Just as an FYI, when replicating to Snowflake using Qlik Replicate, field sizes and order were preserved.
In terms of the tap-oracle re-running discovery each time, below is the setup in my meltano.yml, does anything stand out as amis?
Copy code
extractors:
  - name: tap-oracle
    namespace: tap_oracle
    pip_url: tap-oracle
    executable: tap-oracle
    capabilities:
    - catalog
    - discover
    settings:
    - name: host
      kind: string
    - name: port
      kind: integer
    - name: user
      kind: string
    - name: password
      kind: password
    - name: sid
      kind: string
    config:
      host: alorapd5
      port: 1541
      user: MELTANO
      password: <somepassword>
      sid: EBSARCH
      default_replication_method: FULL_TABLE
    select:
    - ABM-ABM_API_TEMPLATES.*
2 Views
Open in Slack
PreviousNext
Powered by