5 ms·

I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2

I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that. For 3, products like https://materialize.com/ https://materialize.com/ look interesting for being able to create derived materialized views that can efficiently be kept up to date.

fatherzine 4y ago
"someone could design a functional-style programming language that compiles to SQL". See e.g. R dplyr
sbuttgereit 4y ago
I'm currently doing work that uses Elixir's Ecto which goes a great deal towards what I think you're aiming at. I can write my SQL in a familiar, yet functional and composable style; while knowing what SQL will be produced in the end. Ecto, as I understand it, was inspired by Microsoft's/c# LINQ. I've not worked with that, but heard similar praises for that as exists with Ecto. I'm saying this as with most of my experience being in SQL.
coob 4y ago
DBT solves 2 & 3
alphanumeric0 4y ago
I'm not familiar with Ecto, dplyr, or DBT, but I would love an ML-like language to replace SQL. I'm imagining being able to pass a table (or any table-oriented data, like a sub-query) to functions that would type-check columns and would return table-oriented or scalar data. I'm not sure if this is actually possible in practice, but one can dream. For instance, a "top 10" function that could be re-used on any table (apologies for my pseudo types and code): selectTop10 : Column -> Table -> Table selectTop10 orderByColumn table = SQL.selectAllFrom table |> orderDescBy orderByColumn |> limit 10 limit : Int -> Table -> Table limit n rows = SQL.limitBy n rows orderDescBy : Column -> Table -> Table orderDescBy orderByColumn rows = SQL.orderBy [orderByColumn] SQL.Ordering.Desc rows
- chrisjc 4y ago
  Not 100% sure about what you're suggesting, but wouldn't it be easier to pass your functions to your table/sub-query? And that's exactly what you're able to do in most of the modern data warehouse services such as Snowflake. Inferences can be contained within internal/external user defined functions. This is very reminiscent of made the big-data/map-reduce movement so notable, sending your query to the data instead of moving your data to the query. Sending your model to the data, instead of sending the data to the model.
  - alphanumeric0 4y ago
    I think I see what you're saying, and in my idea I'm suggesting the same - sending the function(s) to the data.
- rawgabbit 4y ago
  I don't quite follow what you are saying. In some SQL engines you can use row_number() function and derived tables (or CTE) to get top 10. SELECT Id,col1,col2 FROM ( SELECT Id ,col1 ,col2 ,row_number () over (partition by columnkey1, columnkey2 order by anycolumnwilldohere desc) as _row FROM _table ) as anytablealiaswilldohere WHERE _row <=10
  - alphanumeric0 4y ago
    My idea is you wouldn't have to cut and paste implementations like this. You would be able to just call the function with a column name and table name.
eatonphil 4y ago
> It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. Not exactly but sort of this: http://blog.hydromatic.net/2020/02/25/morel-a-functional-language-for-data.html http://blog.hydromatic.net/2020/02/25/morel-a-functional-lan...

fatherzine 4y ago

"someone could design a functional-style programming language that compiles to SQL". See e.g. R dplyr

sbuttgereit 4y ago

I'm currently doing work that uses Elixir's Ecto which goes a great deal towards what I think you're aiming at. I can write my SQL in a familiar, yet functional and composable style; while knowing what SQL will be produced in the end. Ecto, as I understand it, was inspired by Microsoft's/c# LINQ. I've not worked with that, but heard similar praises for that as exists with Ecto. I'm saying this as with most of my experience being in SQL.

coob 4y ago

DBT solves 2 & 3

alphanumeric0 4y ago

I'm not familiar with Ecto, dplyr, or DBT, but I would love an ML-like language to replace SQL. I'm imagining being able to pass a table (or any table-oriented data, like a sub-query) to functions that would type-check columns and would return table-oriented or scalar data. I'm not sure if this is actually possible in practice, but one can dream. For instance, a "top 10" function that could be re-used on any table (apologies for my pseudo types and code): selectTop10 : Column -> Table -> Table selectTop10 orderByColumn table = SQL.selectAllFrom table |> orderDescBy orderByColumn |> limit 10 limit : Int -> Table -> Table limit n rows = SQL.limitBy n rows orderDescBy : Column -> Table -> Table orderDescBy orderByColumn rows = SQL.orderBy [orderByColumn] SQL.Ordering.Desc rows

chrisjc 4y ago
Not 100% sure about what you're suggesting, but wouldn't it be easier to pass your functions to your table/sub-query? And that's exactly what you're able to do in most of the modern data warehouse services such as Snowflake. Inferences can be contained within internal/external user defined functions. This is very reminiscent of made the big-data/map-reduce movement so notable, sending your query to the data instead of moving your data to the query. Sending your model to the data, instead of sending the data to the model.
- alphanumeric0 4y ago
  I think I see what you're saying, and in my idea I'm suggesting the same - sending the function(s) to the data.
rawgabbit 4y ago
I don't quite follow what you are saying. In some SQL engines you can use row_number() function and derived tables (or CTE) to get top 10. SELECT Id,col1,col2 FROM ( SELECT Id ,col1 ,col2 ,row_number () over (partition by columnkey1, columnkey2 order by anycolumnwilldohere desc) as _row FROM _table ) as anytablealiaswilldohere WHERE _row <=10
- alphanumeric0 4y ago
  My idea is you wouldn't have to cut and paste implementations like this. You would be able to just call the function with a column name and table name.

eatonphil 4y ago

> It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. Not exactly but sort of this: http://blog.hydromatic.net/2020/02/25/morel-a-functional-language-for-data.html http://blog.hydromatic.net/2020/02/25/morel-a-functional-lan...