drizk1
commented
4 months ago Hey @kdpsingh I know you're super busy, but real quick, I've been going on a bug finding mission trying to use increasingly complex queries to find bugs. I've been able to replicate nearly all of the answers on this site I found, and I now have a postgres parser that I have been testing here and it works (with ability to switch back and forth) but I was wondering if you might have an idea of place with more complex queries that I can use to further bug search and test.
thank you!
here as an example of one of the more involved queries ive been able to replicate.
@chain start_query_meta(db, :athlete_events) begin
@rename(tf = NOC)
@mutateGold = if_else(Medal == "Gold", 1, 0),
Silver = if_else(Medal == "Silver", 1, 0),
Bronze = if_else(Medal == "Bronze", 1, 0))
@mutate(total_medals = Gold + Silver + Bronze)
@left_join(:noc_regions, NOC, tf)
@group_by(region, Sport)
@summarize(total_sum = sum(total_medals))
@filter(region == "India" )
@slice_max(total_sum)
# @show_query
@collect
endWITH cte_1 AS (
SELECT ID, Name, Sex, Age, Height, Weight, Team, NOC AS tf, Games, Year, Season, City, Sport, Event, Medal
FROM athlete_events),
cte_3 AS (
SELECT ID, Name, Sex, Age, Height, Weight, Team, tf, Games, Year, Season, City, Sport, Event, Medal, CASE WHEN Medal = 'Gold' THEN 1 ELSE 0 END AS Gold, CASE WHEN Medal = 'Silver' THEN 1 ELSE 0 END AS Silver, CASE WHEN Medal = 'Bronze' THEN 1 ELSE 0 END AS Bronze
FROM cte_1),
cte_5 AS (
SELECT ID, Name, Sex, Age, Height, Weight, Team, tf, Games, Year, Season, City, Sport, Event, Medal, Gold, Silver, Bronze, Gold + Silver + Bronze AS total_medals
FROM cte_3),
cte_6 AS (
SELECT cte_5.*, noc_regions.*
FROM cte_5
LEFT JOIN noc_regions ON noc_regions.NOC = cte_5.tf),
cte_7 AS (
SELECT region, Sport, SUM(total_medals) AS total_sum
FROM cte_6
GROUP BY region, Sport
HAVING region = 'India'),
cte_8 AS (
SELECT *, RANK() OVER (
ORDER BY total_sum DESC) AS rank_col
FROM cte_7),
cte_9 AS (
SELECT *
FROM cte_8
WHERE rank_col <= 1)
SELECT *
FROM cte_9 1×4 DataFrame
Row │ region Sport total_sum rank_col
│ String String Int64 Int64
─────┼─────────────────────────────────────
1 │ India Hockey 173 1
kdpsingh
rdboyes
This is a map as guide for what is to come and what others might want to contribute if they feel so inclined.
Macros
mutateselectarrangedistinctfilterslice_min,slice_max,@slice_samplegroup_bycountsummarize/summariseleft_join- will try to sort out syntax differences from tidier.jl.right_joininner_joinrenamewindow_orderwindow_framerelocatefillpivot_longerandpivot_widercollectshow_queryFunctions
expr_to_sqlto useMacrotools.postwalkstart_query_meta- allows query to begin and stores metadata to enable tidy selectioncase_when,if_elsestarts_with,ends_with,contains,str_detectis_missing()replace_missingandmissing_ifas_integer,as_float,as_stringMAX,MIN,SUM,AVG,COUNTasn()insummarizeacrossinsummarizeacrossinmutateFor Postgres/DuckDB specific parser
year,month,day,hour,min,second,floor_datedifftimeneeded for postgres and mysqlstr_detect,str_replace/_all,str_remove/_allcorr,cov,std,varcollect/start_query_metafor postgres/duckdbcopy_toadded support for SQLite and DuckDBconnect()For duckdb directly read in paths/ https without local intermediary
missing something? drop a comment/new issue
Backends currently supported:
DuckDB
SQLite
MySQL (MariaDB supported when using MySQL.jl too)
Postgres
MSSQL
Clickhouse
Google Big Query
AWS
Oracle
Snowflake
databricks Up next:
Oracle via Oracle.jl? Vs ODBC.jl this discussion suggests it could work for both this and redshift. But need to test
Hive/Impala?
Druid? Druid.jl library hasnt changed much in years..but it should work?no current or intended maintenance per the author. Could be done thru rest api if interest is thereRedshift? it looks like libpq may enable redshift connecting so this could be pretty straightforward vs ODBC.jl which for either case would mean just adding a new parser
Spark would be nice .. but using Spark.jl requires folks to add things from terminal and may lead to build issues for the general user.. so im a bit apprehensive to add it as a dependency