So I would need to convert some postgres columns, ...
# troubleshootingj
jonas_kalderstam
10/21/2022, 1:43 PMSo I would need to convert some postgres columns, for example
int4rangex_startx_endjonas_kalderstam
10/21/2022, 1:58 PMwhat I want is for the tap to instead of this:
Copy code
SELECT x FROM table Copy code
SELECT lower(x) as x_start, upper(x) as x_end FROM tablev
visch
10/21/2022, 2:30 PMWith existing taps I haven't seen anything. I've thought about this a bit in a slightly different context, but I think it'd be great to have the ability to add adhoc streams to any sql tap like postgres where you could do something like
Copy code
name: tap_postgres:
config:
additional_queries:
- name: xstart_xend
sql: SELECT lower(x) as x_start, upper(x) as x_end FROM tablej
jonas_kalderstam
10/21/2022, 2:31 PMthis is just one one example
jonas_kalderstam
10/21/2022, 2:31 PMthe more prominent one would be our Spatial columns
jonas_kalderstam
10/21/2022, 2:31 PMwhich needs
st_text(...)v
visch
10/21/2022, 2:32 PMDo you want to run custom sql arbitrarily? Or is it something consistent / programable?
j
jonas_kalderstam
10/21/2022, 2:33 PMit's consistent as I know how I want it transformed every time.
currently we use a couple of views in the product database to do the conversion for us. but for reasons we want to stop using views
v
visch
10/21/2022, 2:34 PMI think the additional queries piece would work pretty well then but it'd be a fairly big mod, views are much easier in the existing taps 🤷
j
jonas_kalderstam
10/21/2022, 2:34 PMyeah. then I'll continue hacking on my own sql tap
v
visch
10/21/2022, 2:35 PMI really like the idea of running a few queries I know I want to run up front with the sql. There's a lot of time's that's useful for me especially when I don't have access to create views in the source db
visch
10/21/2022, 2:35 PMor I don't really want the other team controlling the view 🙂
j
jonas_kalderstam
10/21/2022, 2:36 PMexactly..
jonas_kalderstam
10/21/2022, 2:36 PMor rather neglect the view..
a
aaronsteers
10/21/2022, 4:33 PMThere are two paths I can see which we've discussed internally and I think which could help here...
First is to allow additional SQL expressions to be appended to an existing 'stream' - like a calculated column in a mapper but pushed down to the SQL layer so it uses the SQL engine.
Second was to allow user-provided queries as the stream definition itself.
aaronsteers
10/21/2022, 4:36 PMIn both cases, detecting of the new schema types is a relatively difficult problem - but not impossible.
j
jonas_kalderstam
11/04/2022, 10:02 AMI ended up solving this with https://github.com/YouCruit/youcruit-tap-rawpostgresql/
it allows you to write arbitrary SQL to run against the database. And you have to manually define the schema of the stream. This works since we only have one or two columns in a handful of tables which need custom SQL.
jonas_kalderstam
11/04/2022, 10:03 AMthe readme is very boilerplate. The test demonstrates how the meltano config would look: https://github.com/YouCruit/youcruit-tap-rawpostgresql/blob/master/tap_rawpostgresql/tests/test_postgres.py
a
aaronsteers
11/04/2022, 7:36 PMFantastic - thanks for sharing this!
aaronsteers
11/04/2022, 7:39 PMcc @visch, @ken_payne, @edgar_ramirez_mondragon - above new work takes a 'streams' config which can accept 'sql' as an arbitrary query. (Still needs 'schema' manually defined, as we feared.) Definitely a strong start for something we'd eventually also want in the sdk.
v
visch
11/04/2022, 7:42 PMyes I had someone asking for this yesterday (without directly asking) !
For schema during discovery there's some stuff we could do for sure to dynamically make it work (select one record or something), could probably remove the round trip but probably easiest to let sql alchemy take the record and make a schema for us
a
aaronsteers
11/04/2022, 7:50 PMIn the past, I've done odd things like creating a view (temp if supported) so I could scan the metadata from the generated view. Also I've done
CREATE (temp?) TABLE temp_foo AS SELECT ... LIMIT 02 Views