1272388 - Allow databricks clusters to access the tiles redshift database

Status: RESOLVED FIXED

(Data Platform and Tools Graveyard :: Operations, defect, P1)

Description

[Marina Samuel [:emtwo]]

This includes setting up a JDBC driver and perhaps installing this library: https://github.com/databricks/spark-redshift

Comment 1

[Thomas Huelbert]

Is this till needed Marina?

Comment 2

[Marina Samuel [:emtwo]]

It's a "nice to have" but not high priority.

Comment 3

[Blake Imsland [:robotblake]]

We're looking to wire this up to Databricks (but not ATMO). Steps would be as follows.

1. Create a databricks user in redshift with permissions for SELECT from the needed tables.
2. Create an internal NLB in the prod IAM with a target group pointing to the tiles redshift cluster.
3. Create an endpoint service in the prod IAM with a target of the internal NLB.
4. Create an endpoint in the databricks IAM with a target of the created endpoint service.
5. Approve the endpoint request in the prod IAM.
5. Test that connecting to the endpoint from databricks with the new credentials works as expected.

The main reason to use an endpoint service is it lets us tunnel a particular port across AWS's internal network as opposed to setting up / maintaining a NAT that goes over the internet.

Comment 4

[Jon Buckley [:jbuck]]

:bmiroglio - What tables do you need access to in Databricks?

:ulfr - Can I get a r? from secops?

Comment 5

[Ben Miroglio [:bmiroglio]]

For now, I need access to ping_centre_main

Comment 6

[Julien Vehent]

I don't have operational experience with vpc endpoint services, but the documented security properties sounds good to me, so r+ (but please do call out any oddities you find while setting it up so we can learn).

Comment 7

[Jon Buckley [:jbuck]]

:robotblake - Can you setup the VPC endpoint service? I'll create a new user account with access to the ping_centre_main table

Comment 8

[Blake Imsland [:robotblake]]

The endpoint service has been created.

Comment 9

[Jon Buckley [:jbuck]]

Credentials sent

Comment 10

[Ben Miroglio [:bmiroglio]]

I'd like to request access to the `assa_router_events_daily` table.

:jbuck can you add permissions to this table?

Comment 11

[Jon Buckley [:jbuck]]

Done

Comment 12

[Blake Imsland [:robotblake]]

Worked on wiring everything up on Friday but having some networking issues, working to resolve those now.

Comment 13

[Blake Imsland [:robotblake]]

Alright, after fighting with this for another day I've opened a support request with AWS, I can get the endpoint service working when connected to the data IAM but not the databricks IAM, will update here when I hear from them.

Comment 14

[Blake Imsland [:robotblake]]

Got the connections working, will throw up an example notebook and forward along.

Comment 15

[Ben Miroglio [:bmiroglio]]

:jbuck can you additionally add permissions to the `firefox_onboarding_events_daily` and `firefox_onboarding_events2_daily` tables?

Comment 16

[Blake Imsland [:robotblake]]

There's an example notebook using pyspark here: https://dbc-caf9527b-e073.cloud.databricks.com/#notebook/37716, let me know if you have any issues!

Comment 17

[Jon Buckley [:jbuck]]

(In reply to Ben Miroglio [:bmiroglio] from comment #15)
> :jbuck can you additionally add permissions to the
> `firefox_onboarding_events_daily` and `firefox_onboarding_events2_daily`
> tables?

Done

Comment 18

[Blake Imsland [:robotblake]]

I'm going to close this but if you run into any other issues feel free to open another bug!

Comment 19

[Ben Miroglio [:bmiroglio]]

(from slack)

jbuck added access to `assa_events_daily` in addition to the tables mentioned above.

Comment 20

[Su-Young Hong]

Hi, it seems that this workaround for accessing tables in the Tiles DB isn't working anymore (was working previously).

• example of attempt to use: https://dbc-caf9527b-e073.cloud.databricks.com/#notebook/51576/command/51578

seeing this in the errors: Caused by: org.postgresql.util.PSQLException: ERROR: relation "assa_impresson_stats_daily" does not exist

I know there was a reset of AWS credentials recently, is this related to it?

Comment 21

[Jon Buckley [:jbuck]]

:shong - I think that's just a typo in the table name, missing the i in impression. Can you fix that and confirm?

Comment 22

[Su-Young Hong]

Hi Jon,

Yes, you're right. I had a typo in that example, sorry about that!

So I'm able to access the tiles DBs using this methodology, but I'm running into issues when the data size gets moderately large (small enough that spark should normally be able to handle it easily). Example below:

• Notebook to Show Issues With Tiles DB Workaround

I'm able to work around it for now by limiting my columns, but this could become a limiting issue sooner rather then later (a number of things related to Ridgeline like snippets data are contained in Tiles). We might need a long term solution that gives Databricks direct access to the database like we do with the regular Telemetry data

Thank you!

Comment 23

[Blake Imsland [:robotblake]]

The real solution here is probably to migrate all of this data into BigQuery, :jbuck is that something we're looking at doing at some point?

Comment 24

[Nan Jiang [:nanj]]

(In reply to Blake Imsland [:robotblake] from comment #23)

The real solution here is probably to migrate all of this data into BigQuery, :jbuck is that something we're looking at doing at some point?

Yes, we've already migrated part of the AS telemetry to BQ, the rest will be done shortly in Q3&Q4.

Comment 25

[Blake Imsland [:robotblake]]

Excellent! 👏

Comment 26

[Jon Buckley [:jbuck]]

:shong - Lets work through this issue with Redshift in a new bug. I tried accessing that notebook, but it says I don't have View permissions on it

Comment 27

[Su-Young Hong]

Excellent. Thank you everyone.

Yes, I see that it's restricted, oversight on my part.

• Su

Comment 28

[Su-Young Hong]

update: the Tiles access methodology provided by blake in comment 16 has been deprecated (the pw will not work).

use the new method using dbutils secrets provided below going forward:

• new method of accessing tiles