https://datahubproject.io logo
Join Slack
Powered by
Ingest | Redshift-usage | Web UI :thread: Error i...
# troubleshoot
w
Ingest | Redshift-usage | Web UI 🧵 Error ingestion redshift-usage through web ui.
We are ingesting redshift data and redshift usage. When execute the second one on the web ui, the next error happen.
Copy code
"ConfigurationError: redshift-usage is disabled; try running: pip install 'acryl-datahub[redshift-usage]'\n",
The server was created with the quickstart guide 
datahub docker quickstart
Previously created a venv with quickstart sentences to install and in addition installed all packages 
datahub-rest, redshift, glue, redshift-usage
After that problem, we altered the quickstart 
requirements.txt
but also have the same error.
Redshift custom permission were given.
Any idea?
b
@early-lamp-41924 This was recently fixed right?
@wooden-football-7175 I think this was an issue affecting some previous version of the DataHub CLI unfortunately.... If you update to the most recent version (0.8.26.3) this should be resolved. Here's the relevant PR: https://github.com/acryldata/datahub/commit/47db7b5e2c89a6fb5d47bdd74af4c56cabf504ed You can do this by pulling new HEAD containers (for quickstart) or overriding the CLI version used when ingesting metadata under the 'Advanced' dropdown as discussed here: https://datahubproject.io/docs/ui-ingestion/#advanced-running-with-a-specific-cli-version
w
Hello @big-carpet-38439. Thanks for your answer. I guess that I have the last version of web IU. I made a prune a reinstalled from the scratch.
It’s weird because only happening with 
redshift-usage
b
Can you try forcing the version of the CLI to 0.8.26.3 by changing the default CLI version in the "Advanced" dropdown of the "Name Ingestion Source" step?
☝️ 1
😃 1
✅ 1
Please let me know if this also does not work
w
Something strange is happening now, I forced and worked also from web and 
datahub ingest
, but on the execution only 1 table is captured (always the same)
WEB EXIT
Copy code
'Source (redshift-usage) report:\n'
           "{'workunits_produced': 1, 'workunit_ids': ['2022-02-15T00:00:00-db.amazon.settlement_report'], 'warnings': {}, 'failures': {}}\n"
           'Sink (datahub-rest) report:\n'
           "{'records_written': 1,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': datetime.datetime(2022, 2, 16, 17, 12, 50, 249168),\n"
           " 'downstream_end_time': datetime.datetime(2022, 2, 16, 17, 12, 50, 268567),\n"
           " 'downstream_total_latency_in_seconds': 0.019399}\n"
           '\n'
           'Pipeline finished successfully\n',
CLI EXIT
Copy code
Source (redshift-usage) report:
{'failures': {}, 'warnings': {}, 'workunit_ids': ['2022-02-15T00:00:00-db.amazon.settlement_report'], 'workunits_produced': 1}
Sink (datahub-rest) report:
{'downstream_end_time': None,
 'downstream_start_time': None,
 'downstream_total_latency_in_seconds': None,
 'failures': [],
 'records_written': 0,
 'warnings': []}
do you understand what is happening?
b
I see - It's only pulling for one table... Hm. Can you share the rest of the configuration recipe?
w
sure
Copy code
source:
    type: redshift-usage
    config:
        host_port: 'host:port'
        database: db
        username: user
        password: '${FSECRET}'
        env: PROD
        email_domain: <http://domain.com|domain.com>
sink:
    type: datahub-rest
    config:
        server: '<http://host:8080>'
consider that I replaced sensible information with wildcards
but run OK
I also tried with more options:
Copy code
# start_time: "2021-01-01T00:00:00Z"
        end_time: 
        env: ENV
        table_pattern:
            allow: ["*"]
        # schema_pattern:
        #     allow: ["*.schema.table"]
        #     deny: ["*schema*", "*shcmea2*", "db.schema.settlement_report"]
Nothing changed
b
How many databases are you expecting inside of that DB?
Recall that this simply scans the Redshift usage log for information (e.g queries) based on the past 1 day by default
If you do not have much query activity in the past day you might not see much. @dazzling-judge-80093 To keep me honest!
w
Thanks john! I have 1 database, +10 schemas, 100 tables. But the query activity is active! Not only 1 table. Increased also 
days
with the 
start_time
but no changes!!
d
We use the 
stl_query
system table to get queries and this system table has a 2-5 days of history based on how busy is your cluster. -> https://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERY.html
w
I run this query extracted from 
redshift-usage.py
and return a lots of rows
Copy code
SELECT DISTINCT ss.userid,
       ss.query,
       sui.usename,
       ss.tbl,
       sq.querytxt,
       sti.database,
       sti.schema,
       sti.table,
       sq.starttime,
       sq.endtime,
       sq.aborted
FROM stl_scan ss
  JOIN svv_table_info sti ON ss.tbl = sti.table_id
  JOIN stl_query sq ON ss.query = sq.query
  JOIN svl_user_info sui ON sq.userid = sui.usesysid
WHERE sq.aborted = 0
ORDER BY ss.endtime DESC;
d
ahh, it can happen that your user does not have proper rights and then it will only see their queries
I don’t know if you run datahub with the same user or different oen
one
https://datahubproject.io/docs/metadata-ingestion/source_docs/redshift#prerequisites -> this is the permission which need for the user you run usage
w
I run this query with the same user that datahub does
and applied the additional privilege
d
I will try locally
✅ 1
w
Yesterday I created a query adhoc against another table, and keep fetchjing the same one
let me know if I can help with anything else
There is any “tutorial that I can follow to debug the code with pytest or something like that? May be there I can realized what I am doing wrong!!
d
I tried it with querying two table and getting usage and on my side I can see both query show up on the proper dataset page. All the tables are ingested where you want to load usage, right?
w
yes
Copy code
userid|query  |usename              |tbl   |querytxt    |database |schema           |table                             |starttime              |endtime                |aborted|
------+-------+---------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------------+---------------------------------------------------+-----------------------+-----------------------+-------+
   107|3866410|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:23:59.782|2022-02-16 18:23:59.870|      0|
   107|3866408|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:23:54.119|2022-02-16 18:23:54.207|      0|
   107|3866404|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:23:37.931|2022-02-16 18:23:42.440|      0|
   107|3866363|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:20:23.860|2022-02-16 18:20:28.576|      0|
   107|3866359|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:20:11.544|2022-02-16 18:20:11.609|      0|
   107|3866254|                     |388568|SELECT CAST(|         |analytics_agg    |                                  |2022-02-16 18:14:55.116|2022-02-16 18:15:04.481|      0|
   107|3866256|                     |388568|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:14:55.367|2022-02-16 18:15:04.464|      0|
   107|3866252|                     |388568|SELECT CAST(|         |analytics_agg    |                                  |2022-02-16 18:14:55.088|2022-02-16 18:15:01.713|      0|
   107|3866251|                     |388568|SELECT CAST(|         |analytics_agg    |                                  |2022-02-16 18:14:55.083|2022-02-16 18:15:01.329|      0|
   107|3866253|                     |388568|WITH "qt_0" |         |analytics_agg    |                                  |2022-02-16 18:14:55.099|2022-02-16 18:15:01.194|      0|
   107|3866246|                     |388568|WITH "qt_0" |         |analytics_agg    |                                  |2022-02-16 18:14:55.000|2022-02-16 18:15:00.444|      0|
   107|3866257|                     |388568|WITH "qt_0" |         |analytics_agg    |                                  |2022-02-16 18:15:00.135|2022-02-16 18:15:00.400|      0|
   107|3866250|                     |388568|WITH "qt_0" |         |analytics_agg    |                                  |2022-02-16 18:14:55.071|2022-02-16 18:14:59.936|      0|
   107|3866241|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:14:36.484|2022-02-16 18:14:42.388|      0|
   107|3866238|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:14:21.729|2022-02-16 18:14:26.149|      0|
   107|3866234|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:14:13.023|2022-02-16 18:14:17.680|      0|
   107|3866232|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:14:03.163|2022-02-16 18:14:07.613|      0|
   107|3866223|                     |388538|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:13:46.066|2022-02-16 18:13:46.151|      0|
   107|3866221|                     |388570|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:13:39.783|2022-02-16 18:13:39.856|      0|
   107|3866208|                     |388570|SELECT DATE_|         |analytics_agg    |                                  |2022-02-16 18:12:42.430|2022-02-16 18:12:42.459|      0|
   107|3866206|                     |388570|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:12:38.827|2022-02-16 18:12:38.892|      0|
   107|3866203|                     |388570|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:12:33.817|2022-02-16 18:12:38.890|      0|
   107|3866200|                     |388570|SELECT "ta_1|         |analytics_agg    |                                  |2022-02-16 18:12:21.047|2022-02-16 18:12:21.313|      0|
   107|3866199|                     |388538|SELECT DATE_|         |analytics_agg    |                                  |2022-02-16 18:12:20.998|2022-02-16 18:12:21.273|      0|
   107|3866197|                     |388538|SELECT DATE_|         |analytics_agg    |                                  |2022-02-16 18:12:20.990|2022-02-16 18:12:21.255|      0|
i deleted the table name, but no one is showing usage
d
hmm, usename is missing or am I wrong?
Here is an actual query we run:
Copy code
SELECT DISTINCT ss.userid,
       ss.query,
       sui.usename,
       ss.tbl,
       sq.querytxt,
       sti.database,
       sti.schema,
       sti.table,
       sq.starttime,
       sq.endtime,
       sq.aborted
FROM stl_scan ss
  JOIN svv_table_info sti ON ss.tbl = sti.table_id
  JOIN stl_query sq ON ss.query = sq.query
  JOIN svl_user_info sui ON sq.userid = sui.usesysid
WHERE ss.starttime >= '2020-12-15 20:08:23'
AND ss.starttime < '2023-12-15 20:08:23'
AND sq.aborted = 0
ORDER BY ss.endtime DESC;
{ “SELECT DISTINCT ss.userid,\n    ss.query,\n    sui.usename,\n    ss.tbl,\n    sq.querytxt,\n    sti.database,\n    sti.schema,\n    sti.table,\n    sq.starttime,\n    sq.endtime,\n    sq.aborted\nFROM stl_scan ss\n JOIN svv_table_info sti ON ss.tbl = sti.table_id\n JOIN stl_query sq ON ss.query = sq.query\n JOIN svl_user_info sui ON sq.userid = sui.usesysid\nWHERE ss.starttime >= ‘2020-12-15 200823’\nAND ss.starttime < ‘2023-12-15 200823’\nAND sq.aborted = 0\nORDER BY ss.endtime DESC”: [ { “userid” : 100, “query” : 2799459, “usename” : “testuser”, “tbl” : 101596, “querytxt” : “select * from public.event e limit 100", “database” : “dev”, “schema” : “public”, “table” : “event”, “starttime” : “2022-02-17T122641.635Z”, “endtime” : “2022-02-17T122641.790Z”, “aborted” : 0 }, { “userid” : 100, “query” : 2799413, “usename” : “testuser”, “tbl” : 101591, “querytxt” : “select * from public.category c limit 100", “database” : “dev”, “schema” : “public”, “table” : “category”, “starttime” : “2022-02-17T122453.915Z”, “endtime” : “2022-02-17T122454.054Z”, “aborted” : 0 } ]}
the missing username can be a problem because we drop records without username
w
also dropped username, but it has
d
ahh, cool, sorry
w
no appologies needed please, you are trying to help
there is no chance that the last version is not iteration against the batch??
d
in your example query result 
analytics_agg
is a table?
❌ 1
w
it’s a redshift schema
but always get the same table … it’s kind of weird
and if I run with dev, get a “diffent table” but always the same!!!
same behavior than production, with a different table
d
and you have all the tables ingested already, right?
w
no filters to the services … ingest all the redshift databases and tables
I looked on the docker actions logs, and no “hide error” happened
d
do you see multiple queries for that same table?
w
yes
sorry
for the ONE that is ingested, only 1 query
d
Can you run this query? I wonder how many matches there are?
Copy code
select count(*) from (
SELECT DISTINCT ss.userid,
       ss.query,
       sui.usename,
       ss.tbl,
       sq.querytxt,
       sti.database,
       sti.schema,
       sti.table,
       sq.starttime,
       sq.endtime,
       sq.aborted
FROM stl_scan ss
  JOIN svv_table_info sti ON ss.tbl = sti.table_id
  JOIN stl_query sq ON ss.query = sq.query
  JOIN svl_user_info sui ON sq.userid = sui.usesysid
WHERE ss.starttime >= '2020-12-15 20:08:23'
AND ss.starttime < '2023-12-15 20:08:23'
AND sq.aborted = 0
ORDER BY ss.endtime desc
);
w
d
I try to generate some queries locally and check if it has anything to do with the number of queries
w
ok
I keep alert in case I can do anything
d
thanks
w
I also try diffents approuch
but always get the same result
at least it’s consistent 😃
d
🙂
w
tamas, what’s your receipt configuration for redshift-usage? no spacial props?
d
Copy code
source:
  type: redshift-usage
  config:
    # Coordinates
    host_port: myredshift:5439
    database: dev
    options:
      connect_args:
        sslmode: prefer

    # Credentials
    username: username
    password: password
    start_time: 2020-12-15T20:08:23.091Z
    end_time: 2023-12-15T20:08:23.091Z
    email_domain: <http://test.com|test.com>

# see <https://datahubproject.io/docs/metadata-ingestion/sink_docs/datahub> for complete documentation
sink:
  type: "datahub-rest"
  config:
    server: "<http://localhost:8080>"
✅ 1
w
Copy code
options:
      connect_args:
        sslmode: prefer
I guess that this make a breacking cvhange
d
you don’t have to use that
w
understanding what happened with changes
Copy code
'Sink (datahub-rest) report:\n'
           "{'records_written': 855,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': datetime.datetime(2022, 2, 17, 15, 22, 20, 772669),\n"
           " 'downstream_end_time': datetime.datetime(2022, 2, 17, 15, 22, 45, 606629),\n"
           " 'downstream_total_latency_in_seconds': 24.83396}\n"
           '\n'
           'Pipeline finished successfully\n',
d
Does this mean now it works?
w
yes
d
My other idea is the end time
w
could be
d
I think you did not set it right?
w
I dont
d
Then I think we set 24h interval
w
Copy code
"                  '2022-02-15T00:00:00-db.schema.settlement_report'],\n"
           " 'warnings': {},\n"
           " 'failures': {}}\n"
           'Sink (datahub-rest) report:\n'
           "{'records_written': 855,\n"
           " 'warnings': [],\n"
           " 'failures': [],\n"
           " 'downstream_start_time': datetime.datetime(2022, 2, 17, 15, 26, 12, 119153),\n"
           " 'downstream_end_time': datetime.datetime(2022, 2, 17, 15, 26, 39, 539770),\n"
           " 'downstream_total_latency_in_seconds': 27.420617}\n"
           '\n'
           'Pipeline finished successfully\n',
look this
the LAST ROW, was the only one that was “getting” before I set properly the start and end time
d
Let me check what query we generate if end_time is missing
but I’m happy it finally works
w
thanks man
i’m happy to
I created a PR for airflow to contribute to the community
I hope to be more active from now
d
I hope apart from this you will have smooth experience with Datahub
w
yes … I understand that is growing and a lot a feats are on dev or review
but for the moment we adopted for our modern data stack
👍 1
just to clear my mind, what 
include_operational_stats
do???
I am taking advantage of kindness and knowledge 😃
d
If you enable it then on the dataset page you should be able to see when the dataset was last updated which we calculate based on the last insert operation happened on the table.
w
thanks in advanceeeeee
3 Views
Open in Slack
PreviousNext
Powered by