Re: Is SQL plenty good enough for complex data transformation?

On Nov 15, 12:58 pm, Robert <irishhac..._at_gmail.com> wrote:
> When doing complex data transformation ( data preparation, data
> manipulation, etc. ) , perhaps when preparing data for statistical
> analysis, or perhaps in an ETL context, is the SQL programming
> language plenty good enough?
>
> For that type of work, can you be just as productive using SQL syntax,
> and not using at all the SAS or SPSS programming languages?
>
> I think that the pitch of the ELT folks ( ELT vs ETL) is : with the
> SQL programming language, there is no use for any other data
> manipulation programming language.

Depends on whether the transformations you are doing are easily supported by the language. Many transformations are difficult to describe in relational terms, so there is a built-in tension between different subsets of the language, even by one vendor. Oracle has an excellent set of language extensions. However, I do some things that are not easily supported by them in a performant manner. These things tend to be "bad design," or more accurately, "insane requirements," such as the same entities being defined in different manners by different users - for example, massive reorganization of the data, but some people want to see new data as if it were the old data, and others want to rewrite history. But you can't limit what people are wanting to see and willing to pay for by language limitations or bias, that would be backwards. So, sometimes even ksh, unix sort and sqlloader win, even if generally less productive. The productivity of the most productive environment can be blown by something just outside the problem space it is designed for.

But I may have a minority opinion. I used to consider Celko's puzzlers defacto proof that some commonly desired things are unnecessarily hard in SQL.

jg

--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/24.90.html#subj7