https://datahubproject.io logo
Join Slack
Powered by
Hello, all! How do I import data excluding specifi...
# ingestion
b
Hello, all! How do I import data excluding specific columns when ingesting data? I am using MSSQL, but some columns are personal information. I don't want to bring sample data from the personal information column, and I want to bring sample data from the rest of the columns. Anyone know?
h
You can use 
profiling.allow_deny_patterns
to set deny patterns for columns to exclude: For example to omit profiling columns 
FirstName
and 
LastName
from table 
DemoDataAlias.Foo.Persons
, you can use config :
Copy code
profiling:
        allow_deny_patterns:
            deny:
                - "DemoDataAlias.Foo.Persons.FirstName"
                - "DemoDataAlias.Foo.Persons.LastName"
b
Oh! I See!! Thanks a lot! I will try right away!
Hi, Mayuri! Can I ask you one more thing? I tried allow_deny_patterns but heading to error below!
Copy code
'[2022-06-30 08:30:12,881] INFO     {datahub.cli.ingest_cli:96} - DataHub CLI version: 0.8.33\n'
           '[2022-06-30 08:30:12,900] INFO     {datahub.ingestion.sink.datahub_rest:60} - Setting gms config\n'
           '2 validation errors for SQLServerConfig\n'
           'profiling -> allow_deny_patterns\n'
           '  none is not an allowed value (type=type_error.none.not_allowed)\n'
           'profiling -> deny\n'
           '  extra fields not permitted (type=value_error.extra)\n',
           "2022-06-30 08:30:17.421545 [exec_id=0287b013-9a43-41a7-a712-0b9e18c8b67d] INFO: Failed to execute 'datahub ingest'",
           '2022-06-30 08:30:17.421931 [exec_id=0287b013-9a43-41a7-a712-0b9e18c8b67d] INFO: Caught exception EXECUTING '
           'task_id=0287b013-9a43-41a7-a712-0b9e18c8b67d, name=RUN_INGEST, stacktrace=Traceback (most recent call last):\n'
my recipe was like this.
source: type: mssql config: host_port: 'IP' database: database_name username: datahub password: datahub include_tables: true include_views: true profiling: enabled: true allow_deny_patterns: deny: - "JOBS.job_id" sink: type: datahub-rest config: server: 'http://datahub-datahub-gms:8080'
h
sure, looks like your recipe formatting is messed. change this profiling: enabled: true allow_deny_patterns: deny: - "JOBS.job_id" to profiling: enabled: true allow_deny_patterns: deny: - "JOBS.job_id"
b
what is the difference?
OH!!! I see!
👍 1
however I can see job_id 's sample data...
h
deny needs to be -> fully qualified table name+column name like my example database.schema.table.column
b
(anyway after changed the space the JOB was successful)
👍 1
OH!!
Okay! Thanks!
I will try again!
h
Hey, did it work ?
b
Hello!!! I wasn't able to watch it on the weekend because I was on vacation! Anyway It didn't work well.
source: type: mssql config: host_port: 'ip' database: dbname username: datahub password: datahub include_tables: true include_views: true profiling: enabled: true allow_deny_patterns: deny: - "JOBMNG.dbo.JOB_STEPS.job_id" sink: type: datahub-rest config: server: 'http://datahub-datahub-gms:8080'
The status was Succeeded however
I can see the sample value of job_id... 😂
I Thing I should add table_pattern..
datahub UI told me that "Found invalid YAML. Please check your recipe configuration."
h
Hey, can you confirm yaml is formatted correctly. You can use online yaml validator like https://codebeautify.org/yaml-validator Also, consider using profile_pattern config directly to provide columns to ignore during profiling. I just noticed that profile_pattern overrides profiling.allow_deny_patterns, hence earlier configs weren't filtering out columns, as expected. Like this -
Copy code
source:
    type: mssql
    config:
        host_port: 'ip'
        database: dbname
        username: datahub
        password: datahub
        include_tables: true
        include_views: true
        profiling:
            enabled: true
        profile_pattern:
            deny:
                - "JOBMNG.dbo.JOB_STEPS.job_id"
sink:
    type: datahub-rest
    config:
        server: '<http://datahub-datahub-gms:8080>'
b
Thank you! I will try! I tried below
source: type: mssql config: host_port: 'ip' database: dbname username: datahub password: datahub include_tables: true include_views: true profiling: enabled: true allow_deny_patterns: allow: - JOBMNG.OPERATORS.operator_id deny: - JOBMNG.OPERATORS.hp_no - JOBMNG.OPERATORS.email table_pattern: allow: - .OPERATORS sink: type: datahub-rest config: server: 'http://datahub-datahub-gms:8080'
and find out not showing schema but still shows sample data!
anyway I will try again!
h
you can also use regex. table_pattern config expects pattern to match with qualified table name so you will need to give 
table_pattern.allow
as 
".*OPERATORS"
to ingest only that table Similarly , To ignore column _job_id_ from profiling, you can give 
profile_pattern.deny
as 
".*job_id"
b
yeap! I will try again!
h
Hey, did it work ?
b
unfortunately failed...Still can see the sample values of hp_no & email (which I use regex deny)
h
If you can show me the recipe and what is exactly that you want to achieve, I can help you out. I have tried profile_pattern for column myself, and it did work !
b
Thanks a lot!
What i want : Schema : Show all Stats : Show all except hp_no / email
Show all except hp_no / email <- This part is unresolved...
h
how are you setting profile pattern currently ?
b
that was the result of below
source: type: mssql config: host_port: '172.30.217.221:3950' database: JOBMNG username: datahub password: eldpdl12!@ include_tables: true include_views: true profiling: enabled: true allow_deny_patterns: allow: [] sink: type: datahub-rest config: server: 'http://datahub-datahub-gms:8080'
h
Copy code
config:
    profile_pattern:
        deny:
        - "jobmng.dbo.operators.hp_no"
        - "jobmng.dbo.operators.email"
b
I want to check that if I use allow :[] than nothing collect
but everything was collect
i've tried that but still i can see the stats.sample value
oh, wait
h
unfortunately allow_deny_patterns is not honoured currently, so please use profile_pattern.
b
oh! okay!
I will try right away!
source: type: mssql config: host_port: '1234' database: JOBMNG username: datahub password: !!!! include_tables: true include_views: true profiling: enabled: true include_field_sample_values: true allow_deny_patterns: allow: - JOBMNG.OPERATORS.hp_no - JOBMNG.OPERATORS.email sink: type: datahub-rest config: server: 'http://datahub-datahub-gms:8080'
i've failed that too, I will try profile_pattern
h
you missed the schema name.. dbo OR use wildcard .*
b
ah! oh my..
yes
h
JOBMNG.OPERATORS.hp_no -> this won't work JOBMNG.dbo.OPERATORS.hp_no -> this should work
b
I will try that again
Mayuri, I'm on the way from work, so I'll try it at home and leave a slack, thank you so much!
h
yes yes
b
Thank you sooooo much! talk you later! 😀
oh my god!!!
profiling: enabled: true profile_pattern: deny: - JOBMNG.dbo.OPERATORS_1.hp_no - JOBMNG.dbo.OPERATORS_1.email
profile_pattern: <- this works!!!!!!!!!!!!!!!!
Thank you soooooo much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
One more thing, Is there any other option that I can see the null count, distint count?
To summarize, I want only the sample value to be displayed as unknown and all other items to be visible.
Do you think it is possible?
h
I think its not possible at the moment. If denied using profile pattern, nothing is included for that column. One thing you can do it disable sample values profiling and remove profile pattern altogether but that will remove sample values from all other columns too.
Copy code
profiling:
    include_field_sample_values: False
b
Yeap, I've tried include_field_sample_values: False but as you said and it did remove sample values from all other columns too!
g
Copy code
include_field_sample_values: False
it does not remove other columns now. cool
4 Views
Open in Slack
PreviousNext
Powered by