Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL: One of the most valuable skills

craigkerstiens.com

Post reply on HN
Print view View original

231–240 of 390 posts

Re: SQL: One of the most valuable skills

#231

Post by barrkel »

nostrademons wrote: post #222
barrkel wrote: post #193

Earlier quoted context omitted.

Chasing pointers (on the same medium) is usually slower than the access patterns that databases usually use. First, a database can have hash indexes instead of btree indexes, so lookups can be O(1) too, but it turns out that btrees are often better because they can return range results efficiently, and finding the range in a btree is only logarithmic for the first lookup. If your index is clustered - if it covers the…

Depends a lot on the actual access patterns of your data. Many recent web & mobile apps have a lot of screens where you just want to grab one blob of heterogenous data and format it with the UI toolkit of choice. Or if they do display multiple results, it's O(10) rather than O(1000) or O(1M). Chasing pointers is fine for use-cases like this, because you do it once and you have all the information you're looking for.…

Oh I agree. Though I think having the rest of a relational DB is really nice should you need more than a simple key-value lookup, and not have to cobble it together through application-side joins and denormalization.

The application I work on in my day job does not match the key/value lookup idiom at all. User-defined sorts and filters over user-defined schema, and mass automated operations over data matching certain criteria. If you squint a bit, the app even looks a bit like a database in terms of user actions.

And even relational databases (at least row-oriented with primarily disk storage) have their limit here. With increasing volumes of data, it can't keep up. We can't index all the columns, and indexes can't span multiple tables. We increasingly need more denormalization solutions that convert hotter bits of data into e.g. in-memory caches that are faster for ad-hoc sorts and filters. Database first is a decent place to start, though having a first-class event feed for updates would certainly be nice...

Re: SQL: One of the most valuable skills

#232

Post by ako »

EGreg wrote: post #184
slap_shot wrote: post #67

SQL is one the most amazing concepts I've ever experienced. It's nearly 5 decades old and there is no sign of a replacement. We've created countless other technologies to store and process data, and we always seem to try to re-create SQL in those technologies (e.g. Hive, Presto, KSQL, etc). I run a early stage company that builds analytics infrastructure for companies. We are betting very heavily on SQL, and Craigs p…

Can I ask a simple question about efficiency? It seems to me that graph databases are far more efficient than relational ones for most tasks. That’s because all lookups are O(1) instead of O(log N). That adds up. Also, copying a subgraph is far easier, and so is joining. Think about it, when you shard you are essentially approaching graph databases because your hash or range by which you find your shard is basically…

Graph databases are great for retrieving existing data with associated data.

The power of SQL comes from the fact that you can easily create new information out of the data: create new sets, group by certain features, aggregates on certain features.

It's a lot more powerful than just store and retrieve.

Re: SQL: One of the most valuable skills

#233

Post by zepolen »

tlarkworthy wrote: post #187
nostrademons wrote: post #115

Earlier quoted context omitted.

"we always seem to try to re-create SQL in those languages (e.g. Hive, Presto, KSQL, etc)." This is largely because of the number of non-programmers who know SQL. Add an SQL layer on top of your non-SQL database and you instantly open up a wide variety of reporting & analytics functionality to PMs, data scientists, business analysts, finance people, librarians (seriously! I have a couple librarian-as-in-dead-trees fr…

I think SQL is entrenched by network effects. It has a unique conceptual paradigm, which is why it is hard to learn. But any replacement is also likely to be conceptually strange, but noone is gonna put in the time in to learn something weird unless it has the adoption of SQL. This we are stuck on a local mini.a with SQL. (A pretty good one though)

It is not hard to learn - non programmers use it all the time.

Conceptually SQL is much more simple than programming, it basically reads like english:

  SELECT customer, SUM(total)
  FROM orders
  GROUP BY customer
  WHERE created BETWEEN '2018-01-01' AND '2018-12-31'`
  ORDER BY SUM(total) DESC
Compare that to the programming necessary to implement the above:

  totals = {}
  for row in rows:
      if row.created > '2018-12-31':
          continue
      if row.created
Not to mention the SQL version gets first hand knowledge on available indexes in order to speed up the query.

Now imagine adding an AVG(total) to both the SQL and programming version...

Re: SQL: One of the most valuable skills

#234

Post by haskman »

nostrademons wrote: post #196
3pt14159 wrote: post #186

Earlier quoted context omitted.

I understand your perspective, but I look at it a different way. SQL is troublesome to some programming types because it seems alien to ask what you want instead of telling the computer what to do and I find most programmers, especially ASD-types (who I think have an edge for some situations, like writing certain code in a huge org like Google) find this an unfamiliar and strange way of thinking. You're right about s…

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

Preach! I just started building something like this in Purescript which might interest you - https://github.com/ajnsit/purescript-consequence.

It allows writing things like -

    type PersonRel = Relation (name :: String, age :: Int)
    type EmployeeRel = Relation (employeeName :: String, managerName :: String)

    -- Get all managers older than 60
    oldManagers :: PersonRel
    oldManagers =
      employeeTable
        # renameField (SProxy :: SProxy "managerName") (SProxy :: SProxy "name")
        # join (RProxy :: RProxy (name :: String)) personTable
        # filter (\r -> r.age > 60)
        # project (RProxy :: RProxy (name :: String, age :: Int))

Re: SQL: One of the most valuable skills

#235

Post by nazri1 »

Ha I could say the same thing about my $EDITOR, which happens to be vim. I learned how to use it about 2 decades ago and I still use it almost daily since. Some things are really worth investing your time learning them.

Re: SQL: One of the most valuable skills

#237

Post by ako »

JimmyAustin wrote: post #49

My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything. Data cleaning? SQL. Feature engineering? SQL. Pipelines of stored procedures, stored in other stored procedures. Some of these procedures were so convoluted that they outputted tables with over 700 features, and had queries that were hundreds of lines long. Ev…

There's no reason you can't store your sql/tsql/plsql in version control.

We were doing this 20+ years ago, all code was in csv (we upgraded from rcs to csv), and we had a productized distributed scheduling system that would deploy all the sql scripts every night on a number of oracle databases running from aix, to solaris, to vms, to hpux, to irix, and later linux and windows NT.

Similar like you would now use jenkins to build, deploy and test your java apps.

Developers would never touch the test/production databases, only commit sql to csv. Develop local, sql text files, test on a local or shared development database, and then commit to csv.

Re: SQL: One of the most valuable skills

#238

Post by magicalhippo »

garyclarke27 wrote: post #230
nostrademons wrote: post #196

Earlier quoted context omitted.

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

Views - Create a general top level view, then build more specific views on views using more filters, to go down, ie more granular - then just join these with yet more views to combine, or aggregate to go back up. How is that not composable? If you hit performance issues, they are easily solved by using a few materialized views. Also CTEs and User defined Functions (I use pure SQL functions but in Postgres you can eas…

At least in the DB we're using, Sybase SQLAnywhere, materialized views comes with a hefty price tag. They must be dropped and recreated every time you touch any of the base tables, like adding a column, which in turn requires any indexes on the materialized views to be recreated.

For a few of our customers, that meant that a 15 minute DB change (adding a column) turned into a several hour DB change (rebuilding materialized view).

If they're refreshed immediately, they also incur a penalty on the underlying tables, since any data changes essentially runs a trigger to see if the materialized view needs changes as well.

In our case we had one such immediate refresh materialized view which caused such a performance degradation of an underlying table that we had to find an alternate solution.

Re: SQL: One of the most valuable skills

#239

Post by ako »

haskman wrote: post #234
nostrademons wrote: post #196

Earlier quoted context omitted.

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

Preach! I just started building something like this in Purescript which might interest you - https://github.com/ajnsit/purescript-consequence . It allows writing things like - type PersonRel = Relation (name :: String, age :: Int) type EmployeeRel = Relation (employeeName :: String, managerName :: String) -- Get all managers older than 60 oldManagers :: PersonRel oldManagers = employeeTable # renameField (SProxy :: S…

Resembles R dplyr pipelines using magrittr, although i find the dplyr version easier to read.

Re: SQL: One of the most valuable skills

#240

Post by JohnBooty »

nostrademons wrote: post #196
3pt14159 wrote: post #186

Earlier quoted context omitted.

I understand your perspective, but I look at it a different way. SQL is troublesome to some programming types because it seems alien to ask what you want instead of telling the computer what to do and I find most programmers, especially ASD-types (who I think have an edge for some situations, like writing certain code in a huge org like Google) find this an unfamiliar and strange way of thinking. You're right about s…

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

    > My preferred interface would be something like the 
    > relational algebra where relations are represented 
    > as typed values in the host programming language and 
    > operators are normal method calls (or binary operators, 
    > depending on language flavor) and importantly, 
    > intermediate results can be assigned to variables.
Much if not all of what you're asking for can be done today at the database level with modern SQL affordances (CTEs, etc) and vendor-specific stuff like stored procedures. You can write functions that return tables/resultsets, assign those results to variables, etc.

There's not a host programming language database library that exposes those things in quite the manner you seem to be asking for, but that's not a limitation of these SQL-based RDBMSs themselves.

In the realm of what's possible right now, you could of course write your stored procedures directly in .NET languages (MSSQL) Python (Postgres) etc.

Post reply on HN