Re: [Babel][R] Inclusion of multi-line named code blocks in R code

[Alexander Vorobiev <alexander.vorobiev@gmail.com>]

[-- Attachment #1: Type: text/plain, Size: 8034 bytes --]

Hi Tom,

Thanks for the Worg documentation. I don't have the FSF papers yet, but I
will sign and send them soon.

It looks like the Babel SQL support would benefit from more modular
approach so that adding new engine would not require modifying existing
functions. Perhaps, something like the Emacs sql-mode which provides hooks
and functions for adding database engines. For instance I was able to add
Netezza support easily without touching anything in sql-mode.el. Eventually
I managed to run my Netezza queries from Babel by writing a bash script
which would supply the correct parameters to nzsql and calling it psql :)
but having native Babel support would be so much better.

I also have a looming need to run Hadoop Hive sql queries which is not
supported by Babel (or sql-mode) either so I will be definitely adding hive
to ob-sql as well.

Regards,
Alex


On Mon, Oct 7, 2013 at 10:42 AM, Thomas S. Dye <tsd@tsdye.com> wrote:

> Hi Alex,
>
> I made a first draft of the Babel SQL documentation for Worg.  See
> http://orgmode.org/worg/org-contrib/babel/languages/ob-doc-sql.html
>
> Please edit or augment as you see fit.
>
> I believe you need FSF papers to contribute to ob-sql.el.  If you have
> those, then by all means, please add Netezza support and get the ball
> rolling on sessions.
>
> All the best,
> Tom
>
> Alexander Vorobiev <alexander.vorobiev@gmail.com> writes:
>
> > Hi Tom,
> >
> > Unfortunately I can't have pure SQL output in my org files for two
> reasons:
> >
> > 1. The result set I am dealing with for this particular problem is about
> > 20000 records
> > 2. My SQL server (Netezza, "big data appliance") is not supported by
> > Babel-SQL. I configured sql-mode to work with Netezza but session-based
> SQL
> > is not supported by Babel either. I started adding support for SQL
> sessions
> > to ob-sql.el and it kind of works but the results I am getting are
> > inconsistent and only a small subset of header parameters is supported.
> Of
> > course I haven't tested is with any other database. I can share what I've
> > done if anybody is interested.
> >
> > Regards,
> > Alex
> >
> >
> > On Wed, Sep 18, 2013 at 5:27 PM, Thomas S. Dye <tsd@tsdye.com> wrote:
> >
> >> Aloha Alex,
> >>
> >> My work flow in this situation evaluates the SQL to create an Org-mode
> >> table, which serves as input to the R source code block.
> >>
> >> For me, seeing the SQL output in a table is a sanity check.
> >>
> >> hth,
> >> Tom
> >>
> >> Alexander Vorobiev <alexander.vorobiev@gmail.com> writes:
> >>
> >> > I have R code which submits SQL statements to a database server. Since
> >> the
> >> > SQL is rather complex, I want to put it into a separate code block in
> >> order
> >> > to have proper formatting, syntax highlighting, etc:
> >> >
> >> > #+name: long-sql
> >> > #+begin_src sql
> >> >     select *
> >> >     from many, tables
> >> >     where
> >> >         complex_condition1 = 1,
> >> >         complex_condition2 = 2
> >> > #+end_src
> >> >
> >> > * Load the data to R session
> >> > #+begin_src R :session *R* :noweb yes
> >> >     result <- submit_query('<<long-sql>>')
> >> > #+end_src
> >> >
> >> > Unfortunately, the R block doesn't work. When I open the file
> generated
> >> by
> >> > Babel, I see this:
> >> >
> >> > result <- submit_query('select *
> >> > result <- submit_query('from many, tables
> >> > etc
> >> >
> >> > instead of the one R submit_query call with my SQL statement as an
> >> > argument. Is there anything I can do to achieve that?
> >> >
> >> > Thanks
> >> > Alex
> >> > I have R code which submits SQL statements to a database server. Since
> >> > the SQL is rather complex, I want to put it into a separate code block
> >> > in order to have proper formatting, syntax highlighting, etc:
> >> >
> >> > #+name: long-sql
> >> > #+begin_src sql
> >> > select *
> >> > from many, tables
> >> > where
> >> > complex_condition1 = 1,
> >> > complex_condition2 = 2
> >> > #+end_src
> >> >
> >> > * Load the data to R session
> >> > #+begin_src R :session *R* :noweb yes
> >> > result <- submit_query('<<long-sql>>')
> >> > #+end_src
> >> >
> >> > Unfortunately, the R block doesn't work. When I open the file
> >> > generated by Babel, I see this:
> >> >
> >> > result <- submit_query('select *
> >> > result <- submit_query('from many, tables
> >> > etc
> >> >
> >> > instead of the one R submit_query call with my SQL statement as an
> >> > argument. Is there anything I can do to achieve that?
> >> >
> >> > Thanks
> >> > Alex
> >> >
> >> >
> >>
> >> --
> >> Thomas S. Dye
> >> http://www.tsdye.com
> >>
> > Hi Tom,
> >
> > Unfortunately I can't have pure SQL output in my org files for two
> > reasons:
> >
> > 1. The result set I am dealing with for this particular problem is
> > about 20000 records
> > 2. My SQL server (Netezza, "big data appliance") is not supported by
> > Babel-SQL. I configured sql-mode to work with Netezza but
> > session-based SQL is not supported by Babel either. I started adding
> > support for SQL sessions to ob-sql.el and it kind of works but the
> > results I am getting are inconsistent and only a small subset of
> > header parameters is supported. Of course I haven't tested is with any
> > other database. I can share what I've done if anybody is interested.
> >
> > Regards,
> > Alex
> >
> > On Wed, Sep 18, 2013 at 5:27 PM, Thomas S. Dye <tsd@tsdye.com> wrote:
> >
> >     Aloha Alex,
> >
> >     My work flow in this situation evaluates the SQL to create an
> >     Org-mode
> >     table, which serves as input to the R source code block.
> >
> >     For me, seeing the SQL output in a table is a sanity check.
> >
> >     hth,
> >     Tom
> >
> >
> >
> >     Alexander Vorobiev <alexander.vorobiev@gmail.com> writes:
> >
> >     > I have R code which submits SQL statements to a database server.
> >     Since the
> >     > SQL is rather complex, I want to put it into a separate code
> >     block in order
> >     > to have proper formatting, syntax highlighting, etc:
> >     >
> >     > #+name: long-sql
> >     > #+begin_src sql
> >     > select *
> >     > from many, tables
> >     > where
> >     > complex_condition1 = 1,
> >     > complex_condition2 = 2
> >     > #+end_src
> >     >
> >     > * Load the data to R session
> >     > #+begin_src R :session *R* :noweb yes
> >     > result <- submit_query('<<long-sql>>')
> >     > #+end_src
> >     >
> >     > Unfortunately, the R block doesn't work. When I open the file
> >     generated by
> >     > Babel, I see this:
> >     >
> >     > result <- submit_query('select *
> >     > result <- submit_query('from many, tables
> >     > etc
> >     >
> >     > instead of the one R submit_query call with my SQL statement as
> >     an
> >     > argument. Is there anything I can do to achieve that?
> >     >
> >     > Thanks
> >     > Alex
> >     > I have R code which submits SQL statements to a database server.
> >     Since
> >     > the SQL is rather complex, I want to put it into a separate code
> >     block
> >     > in order to have proper formatting, syntax highlighting, etc:
> >     >
> >     > #+name: long-sql
> >     > #+begin_src sql
> >     > select *
> >     > from many, tables
> >     > where
> >     > complex_condition1 = 1,
> >     > complex_condition2 = 2
> >     > #+end_src
> >     >
> >     > * Load the data to R session
> >     > #+begin_src R :session *R* :noweb yes
> >     > result <- submit_query('<<long-sql>>')
> >     > #+end_src
> >     >
> >     > Unfortunately, the R block doesn't work. When I open the file
> >     > generated by Babel, I see this:
> >     >
> >     > result <- submit_query('select *
> >     > result <- submit_query('from many, tables
> >     > etc
> >     >
> >     > instead of the one R submit_query call with my SQL statement as
> >     an
> >     > argument. Is there anything I can do to achieve that?
> >     >
> >     > Thanks
> >     > Alex
> >     >
> >     >
> >
> >
> >     --
> >     Thomas S. Dye
> >     http://www.tsdye.com
> >
> >
>
> --
> Thomas S. Dye
> http://www.tsdye.com
>

[-- Attachment #2: Type: text/html, Size: 11156 bytes --]