https://slingdata.io logo
Powered by
As an addition to this Q - my setup
# sling-help
d
As an addition to this Q - my setup seems to not be inferring type length for strings at all when reading from file, is that intended behaviour? Usual disclaimer that it's very possible I've just wired something up wrong!
f
hey @Daniel could you share the replication config you're using to infer type length?
d
My replication yaml is ->
Copy code
source: HR_SYSTEM
target: MY_ORG_DWH

env:
  # SLING_KEEP_TEMP: true
  
defaults:
  mode: full-refresh
  single: true
  target_options:
    use_bulk: true # use sqlldr if available

  source_options:
    skip_blank_lines: true
    delimiter: "^"
    escape: "\\"
    header: false

streams:
   "orgUnits_*.txt":
    object: BRONZE.CORE_ORGUNITS
    columns:
      ORGSRUCTURECODE:
      ORGUNITCODE:
      ORGUNITCODEORIG:
      ORGUNITNAME:
      ORGLEVEL:
      ACTIVEIND:
      ORGUNITHEAD:
/
This links to my defs.yaml ->
Copy code
type: dagster_sling.SlingReplicationCollectionComponent
    
attributes:
  connections:
    # Data Warehouse
    MY_ORG_DWH
      type: oracle
      host: "{{ env.DAGSTER_DWH_HOST }}"
      user: "{{ env.DAGSTER_DWH_USER }}"
      password: "{{ env.DAGSTER_DWH_PASSWORD }}"
      service_name: "{{ env.DAGSTER_DWH_SERVICE }}"
      port: "{{ env.DAGSTER_DWH_PORT }}"
      sqlldr_path:  C:/instantclient_23_0/sqlldr

    # File Shares
    HR_SYSTEM
      type: local
      url: file:////remote-files/HrData/

    replications:
    - path: ./replications/hr_system
      translation:
        group_name: "hr_system"
        key: "HR/{{ stream_definition.name.split('/')[-1].replace(' ','_').replace('_*','').replace('*','').upper().split('.')[0]  }}"
        tags:
          source_system: "HR System"   
/
I've redacted some system names there and cut out extra streams
I'm aware the string handling in the key translation is mental, been trying to tidy that up today! We're in the UK so it's closing time for me today, I'll write back to anything further in our morning - thanks again.
f
this is strange:
Copy code
columns:
      ORGSRUCTURECODE:
      ORGUNITCODE:
      ORGUNITCODEORIG:
      ORGUNITNAME:
      ORGLEVEL:
      ACTIVEIND:
      ORGUNITHEAD:
did you mean to define types? See https://docs.slingdata.io/concepts/replication/columns#using-yaml-1 for inferring type lengths this is per design by default, but with 
column_typing.string.max_length
bug fixed, it will use the detected length up to the specified length value. You can also use the 
column_typing.string.length_factor
value to add buffer to the inferred type. You can try using the [dev build](https://github.com/slingdata-io/sling-cli?tab=readme-ov-file#automated-dev-builds) now if you'd like.
d
Morning, Intention here is only to define the column names as the underlying file doesn't have headers Switching to select: syntax (below) appears to generally show the same behaviour but also doesn't actually work on my file
Copy code
select: [ORGSRUCTURECODE, ORGUNITCODE, ORGUNITCODEORIG, ORGUNITNAME, ORGLEVEL, ACTIVEIND, ORGUNITHEAD]
# still results in char(400000) in ctl file but also fails to collect the data from headerless file
I'll give the dev build a go, thanks! Edit: I'll need to wait a while to come back to this so will see in the next release build! Thanks so much again.
sling v1.5.13 appears to be correctly inferring type length now (when prompted slightly with a target_opetions.column_typing.string.max_length set) but still seems to be defaulting to char(400000) in the sql\*loader ctl file.
Copy code
repl.yaml
defaults:
  mode: full-refresh
  single: true
  target_options:
    use_bulk: true # use sqlldr if available
    column_typing:
      string:
        length_factor: 1
        max_length: 255

  source_options:
    skip_blank_lines: true
    delimiter: "^"
    escape: "\\"
    header: false

streams:
  "orgUnits_*.txt":
    object: BRONZE.CORE_ORGUNITS
This results in the final oracle table being sensibly typed but the generated ctl being:
Copy code
.ctl
OPTIONS (
  SKIP=1,
  ROWS=1000,
  PARALLEL=true,
  DIRECT=true,
  SKIP_INDEX_MAINTENANCE=true
)
LOAD DATA
INFILE '/dev/stdin'
APPEND
INTO TABLE "BRONZE"."CORE_ORGUNITS"
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    "COL_001" char(400000) NULLIF "COL_001"=BLANKS,
  "COL_002" ,
  "COL_003" char(400000) NULLIF "COL_003"=BLANKS,
  "COL_004" char(400000) NULLIF "COL_004"=BLANKS,
  "COL_005" char(400000) NULLIF "COL_005"=BLANKS,
  "COL_006" char(400000) NULLIF "COL_006"=BLANKS,
  "COL_007" char(400000) NULLIF "COL_007"=BLANKS,
  "_SLING_LOADED_AT" ,
  "_SLING_STREAM_URL" char(400000) NULLIF "_SLING_STREAM_URL"=BLANKS
)
Translation of type lengths at this stage is imporant because sql\*loader will attempt to pre-allocate memory based on what's set here and, for any moderately sized tabled, that could consume 10's of GB very easily. This appears to be standard behaviour on my setup across both file and database source types - it will decide a sensible type for the resultant table but fallback to char(400k) for the bulk load.
Also appears to be a thought that's been had before -> https://github.com/slingdata-io/sling-cli/issues/446#issuecomment-2493498940
f
OK, thanks will look into this
9 Views
PreviousNext
Powered by