From mboxrd@z Thu Jan 1 00:00:00 1970 From: Alexander Vorobiev Subject: Re: [Babel][R] Inclusion of multi-line named code blocks in R code Date: Mon, 7 Oct 2013 15:58:09 -0500 Message-ID: References: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary=047d7b2edb2521758704e82cea0d Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:40171) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1VTHsx-0003Cj-Jd for emacs-orgmode@gnu.org; Mon, 07 Oct 2013 16:58:37 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1VTHsv-0000VO-GQ for emacs-orgmode@gnu.org; Mon, 07 Oct 2013 16:58:35 -0400 Received: from mail-pa0-x22f.google.com ([2607:f8b0:400e:c03::22f]:52793) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1VTHsu-0000V2-Vp for emacs-orgmode@gnu.org; Mon, 07 Oct 2013 16:58:33 -0400 Received: by mail-pa0-f47.google.com with SMTP id kp14so7735690pab.20 for ; Mon, 07 Oct 2013 13:58:31 -0700 (PDT) In-Reply-To: List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: "Thomas S. Dye" Cc: emacs-orgmode --047d7b2edb2521758704e82cea0d Content-Type: text/plain; charset=ISO-8859-1 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 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 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 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 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('<>') > >> > #+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('<>') > >> > #+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 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 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('<>') > > > #+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('<>') > > > #+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 > --047d7b2edb2521758704e82cea0d Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Hi Tom,

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

It looks like the Babel SQL support would ben= efit from more modular approach so that adding new engine would not require= modifying existing functions. Perhaps, something like the Emacs sql-mode w= hich 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 writi= ng a bash script which would supply the correct parameters to nzsql and cal= ling it psql :) but having native Babel support would be so much better.

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

Regards,
Alex


On Mon, Oct 7, 2013 at 10:42 AM, Thomas S. Dye &l= t;tsd@tsdye.com><= /span> wrote:
Hi Alex,

I made a first draft of the Babel SQL documentation for Worg. =A0See
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. =A0If you have those, then by all means, please add Netezza support and get the ball
rolling on sessions.

All the best,
Tom

Alexander Vorobiev <alex= ander.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 abo= ut
> 20000 records
> 2. My SQL server (Netezza, "big data appliance") is not supp= orted by
> Babel-SQL. I configured sql-mode to work with Netezza but session-base= d SQL
> is not supported by Babel either. I started adding support for SQL ses= sions
> 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 wh= at 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 serv= er. 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
>> > =A0 =A0 select *
>> > =A0 =A0 from many, tables
>> > =A0 =A0 where
>> > =A0 =A0 =A0 =A0 complex_condition1 =3D 1,
>> > =A0 =A0 =A0 =A0 complex_condition2 =3D 2
>> > #+end_src
>> >
>> > * Load the data to R session
>> > #+begin_src R :session *R* :noweb yes
>> > =A0 =A0 result <- submit_query('<<long-sql>&g= t;')
>> > #+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 serv= er. Since
>> > the SQL is rather complex, I want to put it into a separate c= ode block
>> > in order to have proper formatting, syntax highlighting, etc:=
>> >
>> > #+name: long-sql
>> > #+begin_src sql
>> > select *
>> > from many, tables
>> > where
>> > complex_condition1 =3D 1,
>> > complex_condition2 =3D 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.tsdy= e.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 supp= orted 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 intereste= d.
>
> Regards,
> Alex
>
> On Wed, Sep 18, 2013 at 5:27 PM, Thomas S. Dye <tsd@tsdye.com> wrote:
>
> =A0 =A0 Aloha Alex,
>
> =A0 =A0 My work flow in this situation evaluates the SQL to create an<= br> > =A0 =A0 Org-mode
> =A0 =A0 table, which serves as input to the R source code block.
>
> =A0 =A0 For me, seeing the SQL output in a table is a sanity check. >
> =A0 =A0 hth,
> =A0 =A0 Tom
>
>
>
> =A0 =A0 Alexander Vorobiev <alexander.vorobiev@gmail.com> writes:
>
> =A0 =A0 > I have R code which submits SQL statements to a database = server.
> =A0 =A0 Since the
> =A0 =A0 > SQL is rather complex, I want to put it into a separate c= ode
> =A0 =A0 block in order
> =A0 =A0 > to have proper formatting, syntax highlighting, etc:
> =A0 =A0 >
> =A0 =A0 > #+name: long-sql
> =A0 =A0 > #+begin_src sql
> =A0 =A0 > select *
> =A0 =A0 > from many, tables
> =A0 =A0 > where
> =A0 =A0 > complex_condition1 =3D 1,
> =A0 =A0 > complex_condition2 =3D 2
> =A0 =A0 > #+end_src
> =A0 =A0 >
> =A0 =A0 > * Load the data to R session
> =A0 =A0 > #+begin_src R :session *R* :noweb yes
> =A0 =A0 > result <- submit_query('<<long-sql>>&#= 39;)
> =A0 =A0 > #+end_src
> =A0 =A0 >
> =A0 =A0 > Unfortunately, the R block doesn't work. When I open = the file
> =A0 =A0 generated by
> =A0 =A0 > Babel, I see this:
> =A0 =A0 >
> =A0 =A0 > result <- submit_query('select *
> =A0 =A0 > result <- submit_query('from many, tables
> =A0 =A0 > etc
> =A0 =A0 >
> =A0 =A0 > instead of the one R submit_query call with my SQL statem= ent as
> =A0 =A0 an
> =A0 =A0 > argument. Is there anything I can do to achieve that?
> =A0 =A0 >
> =A0 =A0 > Thanks
> =A0 =A0 > Alex
> =A0 =A0 > I have R code which submits SQL statements to a database = server.
> =A0 =A0 Since
> =A0 =A0 > the SQL is rather complex, I want to put it into a separa= te code
> =A0 =A0 block
> =A0 =A0 > in order to have proper formatting, syntax highlighting, = etc:
> =A0 =A0 >
> =A0 =A0 > #+name: long-sql
> =A0 =A0 > #+begin_src sql
> =A0 =A0 > select *
> =A0 =A0 > from many, tables
> =A0 =A0 > where
> =A0 =A0 > complex_condition1 =3D 1,
> =A0 =A0 > complex_condition2 =3D 2
> =A0 =A0 > #+end_src
> =A0 =A0 >
> =A0 =A0 > * Load the data to R session
> =A0 =A0 > #+begin_src R :session *R* :noweb yes
> =A0 =A0 > result <- submit_query('<<long-sql>>&#= 39;)
> =A0 =A0 > #+end_src
> =A0 =A0 >
> =A0 =A0 > Unfortunately, the R block doesn't work. When I open = the file
> =A0 =A0 > generated by Babel, I see this:
> =A0 =A0 >
> =A0 =A0 > result <- submit_query('select *
> =A0 =A0 > result <- submit_query('from many, tables
> =A0 =A0 > etc
> =A0 =A0 >
> =A0 =A0 > instead of the one R submit_query call with my SQL statem= ent as
> =A0 =A0 an
> =A0 =A0 > argument. Is there anything I can do to achieve that?
> =A0 =A0 >
> =A0 =A0 > Thanks
> =A0 =A0 > Alex
> =A0 =A0 >
> =A0 =A0 >
>
>
> =A0 =A0 --
> =A0 =A0 Thomas S. Dye
> =A0 =A0 http://www.= tsdye.com
>
>

--
Thomas S. Dye
http://www.tsdye.com=

--047d7b2edb2521758704e82cea0d--