I am processing some large tables stored in sqlserver that creating an index sometimes reduces the time it takes to run some R script. I am trying to use a mutatefunction dplyrto create a new column ( idx) with a sequential number, and then use this column idxas an index. But the mutant function does not seem to work and constantly gives me this error:
> tbl(channel,'tbl_iris') %>% mutate(idx=1:n())
Error in from:to : NA/NaN argument
In addition: Warning message:
In 1:n() : NAs introduced by coercion\
Right now I am doing what seems to me so stupid to “circumvent” the above error message:
iris <- tbl(channel,'tbl_iris') %>%
collect %>%
mutate(idx=1:n())
try(db_drop_table(channel,'##iris'))
copy_to(channel,iris,'##iris',temporary=FALSE)
db_create_index(channel,'##iris',columns='idx')
Is there a better way to do this? Thank!
Update 01
mutate(idx = row_number()), @Phil, :
> tbl(channel,'##iris') %>%
+ mutate(idx=row_number())
Error: <SQL> 'SELECT TOP 10 "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species", row_number() OVER () AS "idx"
FROM "##iris"'
nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]The function 'row_number' must have an OVER clause with ORDER BY.
> tbl(channel,'##iris') %>%
+ arrange(Species) %>%
+ mutate(idx=row_number())
Error: <SQL> 'SELECT TOP 10 "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species", row_number() OVER (ORDER BY "Species") AS "idx"
FROM (SELECT *
FROM "##iris"
ORDER BY "Species") "kwtundzona"'
nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
02
, @Moody_Mudskipper, ,
> try(db_drop_table(channel,'##iris'))
[1] 0
> copy_to(channel,iris,'##iris',temporary=FALSE)
> tbl(channel,'##iris') %>% head(.,1)
# Source: lazy query [?? x 5]
# Database: Microsoft SQL Server 11.00.6251[dbo@WCDCHCMS9999\CMSAH_DC7_999/data_xx_yyy]
Sepal.Length Sepal.Width Petal.Length Petal.Width Species
<dbl> <dbl> <dbl> <dbl> <chr>
1 5.10 3.50 1.40 0.200 setosa
>
> DBI::dbSendQuery(channel,"ALTER TABLE ##iris ADD idx INT IDENTITY(1,1) NOT NULL")
<OdbcResult>
SQL ALTER TABLE ##iris ADD idx INT IDENTITY(1,1) NOT NULL
ROWS Fetched: 0 [complete]
Changed: 0
> db_create_index(channel,'##iris',columns='idx')
[1] 0
Warning message:
In new_result(connection@ptr, statement) : Cancelling previous query
> tbl(channel,'##iris') %>% head(.,5)
# Source: lazy query [?? x 6]
# Database: Microsoft SQL Server 11.00.6251[dbo@WCDCHCMS9999\CMSAH_DC7_999/data_xx_yyy]
Sepal.Length Sepal.Width Petal.Length Petal.Width Species idx
<dbl> <dbl> <dbl> <dbl> <chr> <int>
1 5.10 3.50 1.40 0.200 setosa 1
2 4.90 3.00 1.40 0.200 setosa 2
3 4.70 3.20 1.30 0.200 setosa 3
4 4.60 3.10 1.50 0.200 setosa 4
5 5.00 3.60 1.40 0.200 setosa 5
script, , .
, , , .
Warning message:
In new_result(connection@ptr, statement) : Cancelling previous query