Dear R wizards: I decided to take the advice in the R data import/export manual and want to learn how to work with SQL for large data sets. I am trying SQLite with the DBI and RSQLite database interfaces. Speed is nice. Alas, I am struggling to find a tutorial that is geared for the kind of standard operations that I would want in R. Simple things: * how to determine the number of rows in a table. (Of course, I could select a row of data and then use this.) * how to insert a new column into my existing SQL table---say, the rank of another variable---and save it back. Am I supposed to create a new data frame, then save it as a new table, then delete the old SQL table? * how to save a revised version of my table in a different sort order (with or without deleting the original table). <-- I guess this is not appropriate, as I should think of SQL tables as unordered. I guess these would make nice little text snippets in the R Data import/export manual, too. help appreciated. regards, /ivo
R 's ability to work with large data sets is limited on PC. I am trying it on cloud to overcome this but need help from other programmers. Also tutorials would help to newbies regards, Ajay On Mon, Aug 25, 2008 at 6:42 PM, ivo welch <ivowel at gmail.com> wrote:> Dear R wizards: > > I decided to take the advice in the R data import/export manual and > want to learn how to work with SQL for large data sets. I am trying > SQLite with the DBI and RSQLite database interfaces. Speed is nice. > Alas, I am struggling to find a tutorial that is geared for the kind > of standard operations that I would want in R. Simple things: > > * how to determine the number of rows in a table. (Of course, I > could select a row of data and then use this.) > > * how to insert a new column into my existing SQL table---say, the > rank of another variable---and save it back. Am I supposed to create > a new data frame, then save it as a new table, then delete the old SQL > table? > > * how to save a revised version of my table in a different sort order > (with or without deleting the original table). <-- I guess this is > not appropriate, as I should think of SQL tables as unordered. > > I guess these would make nice little text snippets in the R Data > import/export manual, too. help appreciated. > > regards, > > /ivo > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >-- Regards, Ajay Ohri http://tinyurl.com/liajayohri
The sqldf package's home page http://sqldf.googlecode.com has quite a few examples as well as links to resources. Also see the examples in ?sqldf On Mon, Aug 25, 2008 at 9:12 AM, ivo welch <ivowel at gmail.com> wrote:> Dear R wizards: > > I decided to take the advice in the R data import/export manual and > want to learn how to work with SQL for large data sets. I am trying > SQLite with the DBI and RSQLite database interfaces. Speed is nice. > Alas, I am struggling to find a tutorial that is geared for the kind > of standard operations that I would want in R. Simple things: > > * how to determine the number of rows in a table. (Of course, I > could select a row of data and then use this.) > > * how to insert a new column into my existing SQL table---say, the > rank of another variable---and save it back. Am I supposed to create > a new data frame, then save it as a new table, then delete the old SQL > table? > > * how to save a revised version of my table in a different sort order > (with or without deleting the original table). <-- I guess this is > not appropriate, as I should think of SQL tables as unordered. > > I guess these would make nice little text snippets in the R Data > import/export manual, too. help appreciated. > > regards, > > /ivo > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >
On Mon, 25 Aug 2008, ivo welch wrote:> Dear R wizards: > > I decided to take the advice in the R data import/export manual and > want to learn how to work with SQL for large data sets. I am trying > SQLite with the DBI and RSQLite database interfaces. Speed is nice. > Alas, I am struggling to find a tutorial that is geared for the kind > of standard operations that I would want in R. Simple things: > > * how to determine the number of rows in a table. (Of course, I > could select a row of data and then use this.)To get number of rows SELECT COUNT(*) FROM table_name For number of columns the quickest reasonably portable way I know is SELECT * FROM table_name LIMIT 1 and then count the columns of the result.> * how to insert a new column into my existing SQL table---say, the > rank of another variable---and save it back. Am I supposed to create > a new data frame, then save it as a new table, then delete the old SQL > table?ALTER TABLE table_name ADD column_name (you may have to/want to specify a data type as well) If you are adding a bunch of columns you might also want to put the new columns in a separate table and JOIN the tables, especially if the need for these extra columns is sporadic or temporary.> * how to save a revised version of my table in a different sort order > (with or without deleting the original table). <-- I guess this is > not appropriate, as I should think of SQL tables as unordered.As you note, SQL tables are conceptually unordered. You can order results of a query as you read them: SELECT foo, bar FROM table_name ORDER BY baz -thomas Thomas Lumley Assoc. Professor, Biostatistics tlumley at u.washington.edu University of Washington, Seattle
stumped again by SQL... If I have a table named "main" in an SQLite data base, how do I get the names of all its columns? (I have a mysql book that claims the SHOW command does this sort of thing, but it does not seem to work on SQLite.) regards, /iaw PS: Thanks for the earlier emails on "warn=2".
On Sun, Aug 31, 2008 at 9:29 PM, ivo welch <ivowel at gmail.com> wrote:> stumped again by SQL... If I have a table named "main" in an SQLite > data base, how do I get the names of all its columns? (I have a mysql > book that claims the SHOW command does this sort of thing, but it does > not seem to work on SQLite.)look up the pragma command.
> Date: Sun, 31 Aug 2008 21:29:38 -0400 > From: "ivo welch" > Subject: Re: [R] SQL Primer for R> stumped again by SQL... If I have a table named "main" in an SQLite > data base, how do I get the names of all its columns? (I have a mysql > book that claims the SHOW command does this sort of thing, but it does > not seem to work on SQLite.)It sounds like SQLite's ".schema" command might be you're looking for. Here's an example: $ sqlite3 foo.db SQLite version 3.5.4 Enter ".help" for instructions sqlite> create table T (c1 integer, c2 integer, c3 integer); sqlite> .tables T sqlite> .schema T CREATE TABLE T (c1 integer, c2 integer, c3 integer); sqlite> .quit Steve Revilak
wow! the answer seems to be "pragma table_info(main);" thanks, Gabor.
ivo welch wrote:> stumped again by SQL... If I have a table named "main" in an SQLite > data base, how do I get the names of all its columns? (I have a mysql > book that claims the SHOW command does this sort of thing, but it does > not seem to work on SQLite.) >If you are using RSQLite, then a lot of the obscure commands are available as R functions. In this case I think you want sqliteTableFields. Duncan Murdoch