2017-12-29 23:18:42 2017-12-29 23:18:42 R version 3.4.2 (2017-09-28) -- "Short Summer" 2017-12-29 23:18:42 Copyright (C) 2017 The R Foundation for Statistical Computing 2017-12-29 23:18:42 Platform: x86_64-w64-mingw32/x64 (64-bit) 2017-12-29 23:18:42 2017-12-29 23:18:42 R is free software and comes with ABSOLUTELY NO WARRANTY. 2017-12-29 23:18:42 You are welcome to redistribute it under certain conditions. 2017-12-29 23:18:42 Type 'license()' or 'licence()' for distribution details. 2017-12-29 23:18:42 2017-12-29 23:18:42 Natural language support but running in an English locale 2017-12-29 23:18:42 2017-12-29 23:18:42 R is a collaborative project with many contributors. 2017-12-29 23:18:42 Type 'contributors()' for more information and 2017-12-29 23:18:42 'citation()' on how to cite R or R packages in publications. 2017-12-29 23:18:42 2017-12-29 23:18:42 Type 'demo()' for some demos, 'help()' for on-line help, or 2017-12-29 23:18:42 'help.start()' for an HTML browser interface to help. 2017-12-29 23:18:42 Type 'q()' to quit R. 2017-12-29 23:18:42 2017-12-29 23:18:42 > setwd( Sys.getenv( "RWD" ) ) 2017-12-29 23:18:42 > 2017-12-29 23:18:42 > machine_specific_replacements <- 2017-12-29 23:18:42 + list( 2017-12-29 23:18:42 + 2017-12-29 23:18:42 + # replace the folder path on macnix 2017-12-29 23:18:42 + c( 'path.expand( \"~\" ) , \"NPPES\"' , paste0( '"' , getwd() , '"' ) ) , 2017-12-29 23:18:42 + 2017-12-29 23:18:42 + # change other things in the script to be run 2017-12-29 23:18:42 + c( "hello" , "howdy" ) 2017-12-29 23:18:42 + 2017-12-29 23:18:42 + ) 2017-12-29 23:18:42 > 2017-12-29 23:18:42 > source( lodown::syntaxtractor( "nppes" , replacements = machine_specific_replacements , setup_test = "setup" ) , echo = TRUE ) 2017-12-29 23:18:43 2017-12-29 23:18:43 > library(lodown) 2017-12-29 23:18:43 2017-12-29 23:18:43 > lodown("nppes", output_dir = file.path("F:/r-wd/nppes-1514585893")) 2017-12-29 23:18:43 building catalog for nppes 2017-12-29 23:18:43 2017-12-29 23:18:44 locally downloading nppes 2017-12-29 23:18:44 2017-12-29 23:18:44 'http://download.cms.gov/nppes/NPPES_Data_Dissemination_December_2017.zip' 2017-12-29 23:18:44 cached in 2017-12-29 23:18:44 'E:/eanthony/r-tmp/nppes/9a211bb6c5e76fcd348bb109431728c3.Rcache' 2017-12-29 23:18:44 copying to 2017-12-29 23:18:44 'E:\eanthony\r-tmp\nppes\RtmpcTVCmS\file984b91be7' 2017-12-29 23:18:44 2017-12-29 23:24:18 QQ: 'CREATE TABLE npi (npi STRING, entity_type_code DOUBLE PRECISION, replacement_npi STRING, employer_identification_number__ein_ STRING, provider_organization_name__legal_business_name_ STRING, provider_last_name__legal_name_ STRING, provider_first_name STRING, provider_middle_name STRING, provider_name_prefix_text STRING, provider_name_suffix_text STRING, provider_credential_text STRING, provider_other_organization_name STRING, provider_other_organization_name_type_code DOUBLE PRECISION, provider_other_last_name STRING, provider_other_first_name STRING, provider_other_middle_name STRING, provider_other_name_prefix_text STRING, provider_other_name_suffix_text STRING, provider_other_credential_text STRING, provider_other_last_name_type_code DOUBLE PRECISION, provider_first_line_business_mailing_address STRING, provider_second_line_business_mailing_address STRING, provider_business_mailing_address_city_name STRING, provider_business_mailing_address_state_name STRING, provider_business_mailing_address_postal_code STRING, provider_business_mailing_address_country_code__if_outside_u_s__ STRING, provider_business_mailing_address_telephone_number STRING, provider_business_mailing_address_fax_number STRING, provider_first_line_business_practice_location_address STRING, provider_second_line_business_practice_location_address STRING, provider_business_practice_location_address_city_name STRING, provider_business_practice_location_address_state_name STRING, provider_business_practice_location_address_postal_code STRING, provider_business_practice_location_address_country_code__if_outside_u_s__ STRING, provider_business_practice_location_address_telephone_number STRING, provider_business_practice_location_address_fax_number STRING, provider_enumeration_date STRING, last_update_date STRING, npi_deactivation_reason_code DOUBLE PRECISION, npi_deactivation_date STRING, npi_reactivation_date STRING, provider_gender_code STRING, authorized_official_last_name STRING, authorized_official_first_name STRING, authorized_official_middle_name STRING, authorized_official_title_or_position STRING, authorized_official_telephone_number STRING, healthcare_provider_taxonomy_code_1 STRING, provider_license_number_1 STRING, provider_license_number_state_code_1 STRING, healthcare_provider_primary_taxonomy_switch_1 STRING, healthcare_provider_taxonomy_code_2 STRING, provider_license_number_2 STRING, provider_license_number_state_code_2 STRING, healthcare_provider_primary_taxonomy_switch_2 STRING, healthcare_provider_taxonomy_code_3 STRING, provider_license_number_3 STRING, provider_license_number_state_code_3 STRING, healthcare_provider_primary_taxonomy_switch_3 STRING, healthcare_provider_taxonomy_code_4 STRING, provider_license_number_4 STRING, provider_license_number_state_code_4 STRING, healthcare_provider_primary_taxonomy_switch_4 STRING, healthcare_provider_taxonomy_code_5 STRING, provider_license_number_5 STRING, provider_license_number_state_code_5 STRING, healthcare_provider_primary_taxonomy_switch_5 STRING, healthcare_provider_taxonomy_code_6 STRING, provider_license_number_6 STRING, provider_license_number_state_code_6 STRING, healthcare_provider_primary_taxonomy_switch_6 STRING, healthcare_provider_taxonomy_code_7 STRING, provider_license_number_7 STRING, provider_license_number_state_code_7 STRING, healthcare_provider_primary_taxonomy_switch_7 STRING, healthcare_provider_taxonomy_code_8 STRING, provider_license_number_8 STRING, provider_license_number_state_code_8 STRING, healthcare_provider_primary_taxonomy_switch_8 STRING, healthcare_provider_taxonomy_code_9 STRING, provider_license_number_9 STRING, provider_license_number_state_code_9 STRING, healthcare_provider_primary_taxonomy_switch_9 STRING, healthcare_provider_taxonomy_code_10 STRING, provider_license_number_10 STRING, provider_license_number_state_code_10 STRING, healthcare_provider_primary_taxonomy_switch_10 STRING, healthcare_provider_taxonomy_code_11 STRING, provider_license_number_11 STRING, provider_license_number_state_code_11 STRING, healthcare_provider_primary_taxonomy_switch_11 STRING, healthcare_provider_taxonomy_code_12 STRING, provider_license_number_12 STRING, provider_license_number_state_code_12 STRING, healthcare_provider_primary_taxonomy_switch_12 STRING, healthcare_provider_taxonomy_code_13 STRING, provider_license_number_13 STRING, provider_license_number_state_code_13 STRING, healthcare_provider_primary_taxonomy_switch_13 STRING, healthcare_provider_taxonomy_code_14 STRING, provider_license_number_14 STRING, provider_license_number_state_code_14 STRING, healthcare_provider_primary_taxonomy_switch_14 STRING, healthcare_provider_taxonomy_code_15 STRING, provider_license_number_15 STRING, provider_license_number_state_code_15 STRING, healthcare_provider_primary_taxonomy_switch_15 STRING, other_provider_identifier_1 STRING, other_provider_identifier_type_code_1 DOUBLE PRECISION, other_provider_identifier_state_1 STRING, other_provider_identifier_issuer_1 STRING, other_provider_identifier_2 STRING, other_provider_identifier_type_code_2 DOUBLE PRECISION, other_provider_identifier_state_2 STRING, other_provider_identifier_issuer_2 STRING, other_provider_identifier_3 STRING, other_provider_identifier_type_code_3 DOUBLE PRECISION, other_provider_identifier_state_3 STRING, other_provider_identifier_issuer_3 STRING, other_provider_identifier_4 STRING, other_provider_identifier_type_code_4 DOUBLE PRECISION, other_provider_identifier_state_4 STRING, other_provider_identifier_issuer_4 STRING, other_provider_identifier_5 STRING, other_provider_identifier_type_code_5 DOUBLE PRECISION, other_provider_identifier_state_5 STRING, other_provider_identifier_issuer_5 STRING, other_provider_identifier_6 STRING, other_provider_identifier_type_code_6 DOUBLE PRECISION, other_provider_identifier_state_6 STRING, other_provider_identifier_issuer_6 STRING, other_provider_identifier_7 STRING, other_provider_identifier_type_code_7 DOUBLE PRECISION, other_provider_identifier_state_7 STRING, other_provider_identifier_issuer_7 STRING, other_provider_identifier_8 STRING, other_provider_identifier_type_code_8 DOUBLE PRECISION, other_provider_identifier_state_8 STRING, other_provider_identifier_issuer_8 STRING, other_provider_identifier_9 STRING, other_provider_identifier_type_code_9 DOUBLE PRECISION, other_provider_identifier_state_9 STRING, other_provider_identifier_issuer_9 STRING, other_provider_identifier_10 STRING, other_provider_identifier_type_code_10 DOUBLE PRECISION, other_provider_identifier_state_10 STRING, other_provider_identifier_issuer_10 STRING, other_provider_identifier_11 STRING, other_provider_identifier_type_code_11 DOUBLE PRECISION, other_provider_identifier_state_11 STRING, other_provider_identifier_issuer_11 STRING, other_provider_identifier_12 STRING, other_provider_identifier_type_code_12 DOUBLE PRECISION, other_provider_identifier_state_12 STRING, other_provider_identifier_issuer_12 STRING, other_provider_identifier_13 STRING, other_provider_identifier_type_code_13 DOUBLE PRECISION, other_provider_identifier_state_13 STRING, other_provider_identifier_issuer_13 STRING, other_provider_identifier_14 STRING, other_provider_identifier_type_code_14 DOUBLE PRECISION, other_provider_identifier_state_14 STRING, other_provider_identifier_issuer_14 STRING, other_provider_identifier_15 STRING, other_provider_identifier_type_code_15 DOUBLE PRECISION, other_provider_identifier_state_15 STRING, other_provider_identifier_issuer_15 STRING, other_provider_identifier_16 STRING, other_provider_identifier_type_code_16 DOUBLE PRECISION, other_provider_identifier_state_16 STRING, other_provider_identifier_issuer_16 STRING, other_provider_identifier_17 STRING, other_provider_identifier_type_code_17 DOUBLE PRECISION, other_provider_identifier_state_17 STRING, other_provider_identifier_issuer_17 STRING, other_provider_identifier_18 STRING, other_provider_identifier_type_code_18 DOUBLE PRECISION, other_provider_identifier_state_18 STRING, other_provider_identifier_issuer_18 STRING, other_provider_identifier_19 STRING, other_provider_identifier_type_code_19 DOUBLE PRECISION, other_provider_identifier_state_19 STRING, other_provider_identifier_issuer_19 STRING, other_provider_identifier_20 STRING, other_provider_identifier_type_code_20 DOUBLE PRECISION, other_provider_identifier_state_20 STRING, other_provider_identifier_issuer_20 STRING, other_provider_identifier_21 STRING, other_provider_identifier_type_code_21 DOUBLE PRECISION, other_provider_identifier_state_21 STRING, other_provider_identifier_issuer_21 STRING, other_provider_identifier_22 STRING, other_provider_identifier_type_code_22 DOUBLE PRECISION, other_provider_identifier_state_22 STRING, other_provider_identifier_issuer_22 STRING, other_provider_identifier_23 STRING, other_provider_identifier_type_code_23 DOUBLE PRECISION, other_provider_identifier_state_23 STRING, other_provider_identifier_issuer_23 STRING, other_provider_identifier_24 STRING, other_provider_identifier_type_code_24 DOUBLE PRECISION, other_provider_identifier_state_24 STRING, other_provider_identifier_issuer_24 STRING, other_provider_identifier_25 STRING, other_provider_identifier_type_code_25 DOUBLE PRECISION, other_provider_identifier_state_25 STRING, other_provider_identifier_issuer_25 STRING, other_provider_identifier_26 STRING, other_provider_identifier_type_code_26 DOUBLE PRECISION, other_provider_identifier_state_26 STRING, other_provider_identifier_issuer_26 STRING, other_provider_identifier_27 STRING, other_provider_identifier_type_code_27 DOUBLE PRECISION, other_provider_identifier_state_27 STRING, other_provider_identifier_issuer_27 STRING, other_provider_identifier_28 STRING, other_provider_identifier_type_code_28 DOUBLE PRECISION, other_provider_identifier_state_28 STRING, other_provider_identifier_issuer_28 STRING, other_provider_identifier_29 STRING, other_provider_identifier_type_code_29 DOUBLE PRECISION, other_provider_identifier_state_29 STRING, other_provider_identifier_issuer_29 STRING, other_provider_identifier_30 STRING, other_provider_identifier_type_code_30 DOUBLE PRECISION, other_provider_identifier_state_30 STRING, other_provider_identifier_issuer_30 STRING, other_provider_identifier_31 STRING, other_provider_identifier_type_code_31 DOUBLE PRECISION, other_provider_identifier_state_31 STRING, other_provider_identifier_issuer_31 STRING, other_provider_identifier_32 STRING, other_provider_identifier_type_code_32 DOUBLE PRECISION, other_provider_identifier_state_32 STRING, other_provider_identifier_issuer_32 STRING, other_provider_identifier_33 STRING, other_provider_identifier_type_code_33 DOUBLE PRECISION, other_provider_identifier_state_33 STRING, other_provider_identifier_issuer_33 STRING, other_provider_identifier_34 STRING, other_provider_identifier_type_code_34 DOUBLE PRECISION, other_provider_identifier_state_34 STRING, other_provider_identifier_issuer_34 STRING, other_provider_identifier_35 STRING, other_provider_identifier_type_code_35 DOUBLE PRECISION, other_provider_identifier_state_35 STRING, other_provider_identifier_issuer_35 STRING, other_provider_identifier_36 STRING, other_provider_identifier_type_code_36 DOUBLE PRECISION, other_provider_identifier_state_36 STRING, other_provider_identifier_issuer_36 STRING, other_provider_identifier_37 STRING, other_provider_identifier_type_code_37 DOUBLE PRECISION, other_provider_identifier_state_37 STRING, other_provider_identifier_issuer_37 STRING, other_provider_identifier_38 STRING, other_provider_identifier_type_code_38 DOUBLE PRECISION, other_provider_identifier_state_38 STRING, other_provider_identifier_issuer_38 STRING, other_provider_identifier_39 STRING, other_provider_identifier_type_code_39 DOUBLE PRECISION, other_provider_identifier_state_39 STRING, other_provider_identifier_issuer_39 STRING, other_provider_identifier_40 STRING, other_provider_identifier_type_code_40 DOUBLE PRECISION, other_provider_identifier_state_40 STRING, other_provider_identifier_issuer_40 STRING, other_provider_identifier_41 STRING, other_provider_identifier_type_code_41 DOUBLE PRECISION, other_provider_identifier_state_41 STRING, other_provider_identifier_issuer_41 STRING, other_provider_identifier_42 STRING, other_provider_identifier_type_code_42 DOUBLE PRECISION, other_provider_identifier_state_42 STRING, other_provider_identifier_issuer_42 STRING, other_provider_identifier_43 STRING, other_provider_identifier_type_code_43 DOUBLE PRECISION, other_provider_identifier_state_43 STRING, other_provider_identifier_issuer_43 STRING, other_provider_identifier_44 STRING, other_provider_identifier_type_code_44 DOUBLE PRECISION, other_provider_identifier_state_44 STRING, other_provider_identifier_issuer_44 STRING, other_provider_identifier_45 STRING, other_provider_identifier_type_code_45 DOUBLE PRECISION, other_provider_identifier_state_45 STRING, other_provider_identifier_issuer_45 STRING, other_provider_identifier_46 STRING, other_provider_identifier_type_code_46 DOUBLE PRECISION, other_provider_identifier_state_46 STRING, other_provider_identifier_issuer_46 STRING, other_provider_identifier_47 STRING, other_provider_identifier_type_code_47 DOUBLE PRECISION, other_provider_identifier_state_47 STRING, other_provider_identifier_issuer_47 STRING, other_provider_identifier_48 STRING, other_provider_identifier_type_code_48 DOUBLE PRECISION, other_provider_identifier_state_48 STRING, other_provider_identifier_issuer_48 STRING, other_provider_identifier_49 STRING, other_provider_identifier_type_code_49 DOUBLE PRECISION, other_provider_identifier_state_49 STRING, other_provider_identifier_issuer_49 STRING, other_provider_identifier_50 STRING, other_provider_identifier_type_code_50 DOUBLE PRECISION, other_provider_identifier_state_50 STRING, other_provider_identifier_issuer_50 STRING, is_sole_proprietor STRING, is_organization_subpart STRING, parent_organization_lbn STRING, parent_organization_tin STRING, authorized_official_name_prefix_text STRING, authorized_official_name_suffix_text STRING, authorized_official_credential_text STRING, healthcare_provider_taxonomy_group_1 STRING, healthcare_provider_taxonomy_group_2 STRING, healthcare_provider_taxonomy_group_3 STRING, healthcare_provider_taxonomy_group_4 STRING, healthcare_provider_taxonomy_group_5 STRING, healthcare_provider_taxonomy_group_6 STRING, healthcare_provider_taxonomy_group_7 STRING, healthcare_provider_taxonomy_group_8 STRING, healthcare_provider_taxonomy_group_9 STRING, healthcare_provider_taxonomy_group_10 STRING, healthcare_provider_taxonomy_group_11 STRING, healthcare_provider_taxonomy_group_12 STRING, healthcare_provider_taxonomy_group_13 STRING, healthcare_provider_taxonomy_group_14 STRING, healthcare_provider_taxonomy_group_15 STRING)' 2017-12-29 23:26:18 II: Finished in 1.99s 2017-12-29 23:38:21 QQ: 'copy 5420943 offset 2 records into npi from 'E:\eanthony\r-tmp\nppes\RtmpcTVCmS\file98475cc769f' using delimiters ',','\n','"' NULL as ''' 2017-12-29 23:59:25 II: Finished in 21.08s 2017-12-29 23:59:25 QQ: 'SELECT COUNT(*) FROM npi' 2017-12-29 23:59:26 II: Finished in 1.11s 2017-12-30 00:00:20 nppes local download completed 2017-12-30 00:00:20 2017-12-30 00:00:20 2017-12-30 00:00:20 > dbdir <- file.path("F:/r-wd/nppes-1514585893", "MonetDB") 2017-12-30 00:00:20 2017-12-30 00:00:20 > warnings() 2017-12-30 00:00:20 NULL 2017-12-30 00:00:20 2017-12-30 00:00:20 > library(DBI) 2017-12-30 00:00:20 2017-12-30 00:00:20 > db <- dbConnect(MonetDBLite::MonetDBLite(), dbdir) 2017-12-30 00:00:24 2017-12-30 00:00:24 > cs <- dbGetQuery(db, " select tables.name, columns.name, location from tables inner join columns on tables.id=columns.table_id left join storage on ..." ... [TRUNCATED] 2017-12-30 00:00:24 QQ: ' select tables.name, columns.name, location from tables inner join columns on tables.id=columns.table_id left join storage on tables.name=storage.table and columns.name=storage.column where location is null and tables.name not in ('tables', 'columns', 'users', 'querylog_catalog', 'querylog_calls', 'querylog_history', 'tracelog', 'sessions', 'optimizers', 'environment', 'queue', 'rejects', 'storage', 'storagemodel', 'tablestoragemodel') ' 2017-12-30 00:00:29 II: Finished in 5.11s 2017-12-30 00:00:29 2017-12-30 00:00:29 > print(cs) 2017-12-30 00:00:29 [1] name name location 2017-12-30 00:00:29 <0 rows> (or 0-length row.names) 2017-12-30 00:00:29 2017-12-30 00:00:29 > stopifnot(nrow(cs) == 0) 2017-12-30 00:00:29 2017-12-30 00:00:29 > dbDisconnect(db, shutdown = TRUE) 2017-12-30 00:00:29 Warning message: 2017-12-30 00:00:29 In file.remove(unzipped_files, tf, tf2) : 2017-12-30 00:00:29 cannot remove file 'E:\eanthony\r-tmp\nppes\RtmpcTVCmS\file984b91be7', reason 'No such file or directory' 2017-12-30 00:00:29 > setup return code=0 2017-12-30 00:00:29 2017-12-30 00:00:29 R version 3.4.2 (2017-09-28) -- "Short Summer" 2017-12-30 00:00:29 Copyright (C) 2017 The R Foundation for Statistical Computing 2017-12-30 00:00:29 Platform: x86_64-w64-mingw32/x64 (64-bit) 2017-12-30 00:00:29 2017-12-30 00:00:29 R is free software and comes with ABSOLUTELY NO WARRANTY. 2017-12-30 00:00:29 You are welcome to redistribute it under certain conditions. 2017-12-30 00:00:29 Type 'license()' or 'licence()' for distribution details. 2017-12-30 00:00:29 2017-12-30 00:00:29 Natural language support but running in an English locale 2017-12-30 00:00:29 2017-12-30 00:00:29 R is a collaborative project with many contributors. 2017-12-30 00:00:29 Type 'contributors()' for more information and 2017-12-30 00:00:29 'citation()' on how to cite R or R packages in publications. 2017-12-30 00:00:29 2017-12-30 00:00:29 Type 'demo()' for some demos, 'help()' for on-line help, or 2017-12-30 00:00:29 'help.start()' for an HTML browser interface to help. 2017-12-30 00:00:29 Type 'q()' to quit R. 2017-12-30 00:00:29 2017-12-30 00:00:30 > setwd( Sys.getenv( "RWD" ) ) 2017-12-30 00:00:30 > 2017-12-30 00:00:30 > machine_specific_replacements <- 2017-12-30 00:00:30 + list( 2017-12-30 00:00:30 + 2017-12-30 00:00:30 + # replace the folder path on macnix 2017-12-30 00:00:30 + c( 'path.expand( \"~\" ) , \"NPPES\"' , paste0( '"' , getwd() , '"' ) ) , 2017-12-30 00:00:30 + 2017-12-30 00:00:30 + # change other things in the script to be run 2017-12-30 00:00:30 + c( "hello" , "howdy" ) 2017-12-30 00:00:30 + 2017-12-30 00:00:30 + ) 2017-12-30 00:00:30 > 2017-12-30 00:00:30 > source( lodown::syntaxtractor( "nppes" , replacements = machine_specific_replacements , setup_test = "test" ) , echo = TRUE ) 2017-12-30 00:00:31 2017-12-30 00:00:31 > library(DBI) 2017-12-30 00:00:31 2017-12-30 00:00:31 > dbdir <- file.path("F:/r-wd/nppes-1514585893", "MonetDB") 2017-12-30 00:00:31 2017-12-30 00:00:31 > db <- dbConnect(MonetDBLite::MonetDBLite(), dbdir) 2017-12-30 00:00:33 2017-12-30 00:00:33 > dbSendQuery(db, "ALTER TABLE npi ADD COLUMN individual INTEGER") 2017-12-30 00:00:33 QQ: 'ALTER TABLE npi ADD COLUMN individual INTEGER' 2017-12-30 00:00:36 II: Finished in 3.48s 2017-12-30 00:00:36 2017-12-30 00:00:36 SQL ALTER TABLE npi ADD COLUMN individual INTEGER 2017-12-30 00:00:36 ROWS Fetched: 0 [complete] 2017-12-30 00:00:36 Changed: 0 2017-12-30 00:00:36 2017-12-30 00:00:36 > dbSendQuery(db, "UPDATE npi \n\tSET individual = \n\t\tCASE WHEN entity_type_code = 1 THEN 1 ELSE 0 END") 2017-12-30 00:00:37 QQ: 'UPDATE npi 2017-12-30 00:00:37 SET individual = 2017-12-30 00:00:37 CASE WHEN entity_type_code = 1 THEN 1 ELSE 0 END' 2017-12-30 00:00:46 II: Finished in 9.66s 2017-12-30 00:00:46 2017-12-30 00:00:46 SQL UPDATE npi 2017-12-30 00:00:46 SET individual = 2017-12-30 00:00:46 CASE WHEN entity_type_code = 1 THEN 1 ELSE 0 END 2017-12-30 00:00:46 ROWS Fetched: 0 [complete] 2017-12-30 00:00:46 Changed: 5420942 2017-12-30 00:00:46 2017-12-30 00:00:46 > dbSendQuery(db, "ALTER TABLE npi ADD COLUMN provider_enumeration_year INTEGER") 2017-12-30 00:00:46 QQ: 'ALTER TABLE npi ADD COLUMN provider_enumeration_year INTEGER' 2017-12-30 00:00:55 II: Finished in 8.97s 2017-12-30 00:00:55 2017-12-30 00:00:55 SQL ALTER TABLE npi ADD COLUMN provider_enumeration_year INTEGER 2017-12-30 00:00:55 ROWS Fetched: 0 [complete] 2017-12-30 00:00:55 Changed: 5420942 2017-12-30 00:00:55 2017-12-30 00:00:55 > dbSendQuery(db, "UPDATE npi \n\tSET provider_enumeration_year = \n\t\tCAST( SUBSTRING( provider_enumeration_date , 7 , 10 ) AS INTEGER )") 2017-12-30 00:00:55 QQ: 'UPDATE npi 2017-12-30 00:00:55 SET provider_enumeration_year = 2017-12-30 00:00:55 CAST( SUBSTRING( provider_enumeration_date , 7 , 10 ) AS INTEGER )' 2017-12-30 00:01:09 II: Finished in 14.14s 2017-12-30 00:01:09 2017-12-30 00:01:09 SQL UPDATE npi 2017-12-30 00:01:09 SET provider_enumeration_year = 2017-12-30 00:01:09 CAST( SUBSTRING( provider_enumeration_date , 7 , 10 ) AS INTEGER ) 2017-12-30 00:01:09 ROWS Fetched: 0 [complete] 2017-12-30 00:01:09 Changed: 5420942 2017-12-30 00:01:09 2017-12-30 00:01:09 > dbGetQuery(db, "SELECT COUNT(*) FROM npi") 2017-12-30 00:01:09 QQ: 'SELECT COUNT(*) FROM npi' 2017-12-30 00:01:09 II: Finished in 0.06s 2017-12-30 00:01:09 L3 2017-12-30 00:01:09 1 5420942 2017-12-30 00:01:09 2017-12-30 00:01:09 > dbGetQuery(db, "SELECT\n\t\tprovider_gender_code ,\n\t\tCOUNT(*) \n\tFROM npi\n\tGROUP BY provider_gender_code") 2017-12-30 00:01:09 QQ: 'SELECT 2017-12-30 00:01:09 provider_gender_code , 2017-12-30 00:01:09 COUNT(*) 2017-12-30 00:01:09 FROM npi 2017-12-30 00:01:09 GROUP BY provider_gender_code' 2017-12-30 00:01:10 II: Finished in 0.53s 2017-12-30 00:01:10 provider_gender_code L3 2017-12-30 00:01:10 1 M 1582291 2017-12-30 00:01:10 2 1366542 2017-12-30 00:01:10 3 F 2472109 2017-12-30 00:01:10 2017-12-30 00:01:10 > dbGetQuery(db, "SELECT AVG( provider_enumeration_year ) FROM npi") 2017-12-30 00:01:10 QQ: 'SELECT AVG( provider_enumeration_year ) FROM npi' 2017-12-30 00:01:10 II: Finished in 0.22s 2017-12-30 00:01:10 L3 2017-12-30 00:01:10 1 2009.771 2017-12-30 00:01:10 2017-12-30 00:01:10 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tAVG( provider_enumeration_year ) AS mean_provider_enumeration_year\n\tFROM npi \n\tGROUP B ..." ... [TRUNCATED] 2017-12-30 00:01:10 QQ: 'SELECT 2017-12-30 00:01:10 provider_gender_code , 2017-12-30 00:01:10 AVG( provider_enumeration_year ) AS mean_provider_enumeration_year 2017-12-30 00:01:10 FROM npi 2017-12-30 00:01:10 GROUP BY provider_gender_code' 2017-12-30 00:01:11 II: Finished in 0.56s 2017-12-30 00:01:11 provider_gender_code mean_provider_enumeration_year 2017-12-30 00:01:11 1 M 2008.700 2017-12-30 00:01:11 2 2009.681 2017-12-30 00:01:11 3 F 2010.503 2017-12-30 00:01:11 2017-12-30 00:01:11 > dbSendQuery(db, "CREATE FUNCTION \n\t\tdiv_noerror(l DOUBLE, r DOUBLE) \n\tRETURNS DOUBLE \n\tEXTERNAL NAME calc.div_noerror") 2017-12-30 00:01:11 QQ: 'CREATE FUNCTION 2017-12-30 00:01:11 div_noerror(l DOUBLE, r DOUBLE) 2017-12-30 00:01:11 RETURNS DOUBLE 2017-12-30 00:01:11 EXTERNAL NAME calc.div_noerror' 2017-12-30 00:01:13 II: Finished in 2.19s 2017-12-30 00:01:13 2017-12-30 00:01:13 SQL CREATE FUNCTION 2017-12-30 00:01:13 div_noerror(l DOUBLE, r DOUBLE) 2017-12-30 00:01:13 RETURNS DOUBLE 2017-12-30 00:01:13 EXTERNAL NAME calc.div_noerror 2017-12-30 00:01:13 ROWS Fetched: 0 [complete] 2017-12-30 00:01:13 Changed: 5420942 2017-12-30 00:01:13 2017-12-30 00:01:13 > dbGetQuery(db, "SELECT \n\t\tis_sole_proprietor , \n\t\tdiv_noerror( \n\t\t\tCOUNT(*) , \n\t\t\t( SELECT COUNT(*) FROM npi ) \n\t\t) AS share_is_sol ..." ... [TRUNCATED] 2017-12-30 00:01:13 QQ: 'SELECT 2017-12-30 00:01:13 is_sole_proprietor , 2017-12-30 00:01:13 div_noerror( 2017-12-30 00:01:13 COUNT(*) , 2017-12-30 00:01:13 ( SELECT COUNT(*) FROM npi ) 2017-12-30 00:01:13 ) AS share_is_sole_proprietor 2017-12-30 00:01:13 FROM npi 2017-12-30 00:01:13 GROUP BY is_sole_proprietor' 2017-12-30 00:01:13 II: Finished in 0.42s 2017-12-30 00:01:13 is_sole_proprietor share_is_sole_proprietor 2017-12-30 00:01:13 1 X 0.02125037 2017-12-30 00:01:13 2 N 0.47691785 2017-12-30 00:01:13 3 0.25208571 2017-12-30 00:01:13 4 Y 0.24974608 2017-12-30 00:01:13 2017-12-30 00:01:13 > dbGetQuery(db, "SELECT SUM( provider_enumeration_year ) FROM npi") 2017-12-30 00:01:13 QQ: 'SELECT SUM( provider_enumeration_year ) FROM npi' 2017-12-30 00:01:14 II: Finished in 0.11s 2017-12-30 00:01:14 L3 2017-12-30 00:01:14 1 10661520988 2017-12-30 00:01:14 2017-12-30 00:01:14 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tSUM( provider_enumeration_year ) AS sum_provider_enumeration_year \n\tFROM npi \n\tGROUP B ..." ... [TRUNCATED] 2017-12-30 00:01:14 QQ: 'SELECT 2017-12-30 00:01:14 provider_gender_code , 2017-12-30 00:01:14 SUM( provider_enumeration_year ) AS sum_provider_enumeration_year 2017-12-30 00:01:14 FROM npi 2017-12-30 00:01:14 GROUP BY provider_gender_code' 2017-12-30 00:01:14 II: Finished in 0.42s 2017-12-30 00:01:14 provider_gender_code sum_provider_enumeration_year 2017-12-30 00:01:14 1 M 3178347273 2017-12-30 00:01:14 2 2512991625 2017-12-30 00:01:14 3 F 4970182090 2017-12-30 00:01:14 2017-12-30 00:01:14 > dbGetQuery(db, "SELECT QUANTILE( provider_enumeration_year , 0.5 ) FROM npi") 2017-12-30 00:01:14 QQ: 'SELECT QUANTILE( provider_enumeration_year , 0.5 ) FROM npi' 2017-12-30 00:01:15 II: Finished in 0.56s 2017-12-30 00:01:15 L3 2017-12-30 00:01:15 1 2008 2017-12-30 00:01:15 2017-12-30 00:01:15 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tQUANTILE( provider_enumeration_year , 0.5 ) AS median_provider_enumeration_year\n\tFROM np ..." ... [TRUNCATED] 2017-12-30 00:01:15 QQ: 'SELECT 2017-12-30 00:01:15 provider_gender_code , 2017-12-30 00:01:15 QUANTILE( provider_enumeration_year , 0.5 ) AS median_provider_enumeration_year 2017-12-30 00:01:15 FROM npi 2017-12-30 00:01:15 GROUP BY provider_gender_code' 2017-12-30 00:01:16 II: Finished in 1.2s 2017-12-30 00:01:16 provider_gender_code median_provider_enumeration_year 2017-12-30 00:01:16 1 M 2007 2017-12-30 00:01:16 2 2008 2017-12-30 00:01:16 3 F 2010 2017-12-30 00:01:16 2017-12-30 00:01:16 > dbGetQuery(db, "SELECT\n\t\tAVG( provider_enumeration_year )\n\tFROM npi\n\tWHERE provider_business_practice_location_address_state_name = 'CA'") 2017-12-30 00:01:16 QQ: 'SELECT 2017-12-30 00:01:16 AVG( provider_enumeration_year ) 2017-12-30 00:01:16 FROM npi 2017-12-30 00:01:16 WHERE provider_business_practice_location_address_state_name = 'CA'' 2017-12-30 00:01:16 II: Finished in 0.06s 2017-12-30 00:01:16 L3 2017-12-30 00:01:16 1 2009.787 2017-12-30 00:01:16 2017-12-30 00:01:16 > dbGetQuery(db, "SELECT \n\t\tVAR_SAMP( provider_enumeration_year ) , \n\t\tSTDDEV_SAMP( provider_enumeration_year ) \n\tFROM npi") 2017-12-30 00:01:16 QQ: 'SELECT 2017-12-30 00:01:16 VAR_SAMP( provider_enumeration_year ) , 2017-12-30 00:01:16 STDDEV_SAMP( provider_enumeration_year ) 2017-12-30 00:01:16 FROM npi' 2017-12-30 00:01:16 II: Finished in 0.25s 2017-12-30 00:01:16 L3 L6 2017-12-30 00:01:16 1 14.8922 3.859041 2017-12-30 00:01:16 2017-12-30 00:01:16 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tVAR_SAMP( provider_enumeration_year ) AS var_provider_enumeration_year ,\n\t\tSTDDEV_SAMP( ..." ... [TRUNCATED] 2017-12-30 00:01:16 QQ: 'SELECT 2017-12-30 00:01:16 provider_gender_code , 2017-12-30 00:01:16 VAR_SAMP( provider_enumeration_year ) AS var_provider_enumeration_year , 2017-12-30 00:01:16 STDDEV_SAMP( provider_enumeration_year ) AS stddev_provider_enumeration_year 2017-12-30 00:01:16 FROM npi 2017-12-30 00:01:16 GROUP BY provider_gender_code' 2017-12-30 00:01:16 II: Finished in 0.28s 2017-12-30 00:01:16 provider_gender_code var_provider_enumeration_year 2017-12-30 00:01:16 1 M 13.48925 2017-12-30 00:01:16 2 13.98318 2017-12-30 00:01:16 3 F 14.97560 2017-12-30 00:01:16 stddev_provider_enumeration_year 2017-12-30 00:01:16 1 3.672772 2017-12-30 00:01:16 2 3.739409 2017-12-30 00:01:16 3 3.869831 2017-12-30 00:01:16 2017-12-30 00:01:16 > dbGetQuery(db, "SELECT \n\t\tCORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) )\n\tFROM npi") 2017-12-30 00:01:16 QQ: 'SELECT 2017-12-30 00:01:16 CORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) ) 2017-12-30 00:01:16 FROM npi' 2017-12-30 00:01:17 II: Finished in 0.73s 2017-12-30 00:01:17 L5 2017-12-30 00:01:17 1 19.22442 2017-12-30 00:01:17 2017-12-30 00:01:17 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tCORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) )\n\tFROM ..." ... [TRUNCATED] 2017-12-30 00:01:17 QQ: 'SELECT 2017-12-30 00:01:17 provider_gender_code , 2017-12-30 00:01:17 CORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) ) 2017-12-30 00:01:17 FROM npi 2017-12-30 00:01:17 GROUP BY provider_gender_code' 2017-12-30 00:01:18 II: Finished in 0.88s 2017-12-30 00:01:18 provider_gender_code L5 2017-12-30 00:01:18 1 M 0 2017-12-30 00:01:18 2 0 2017-12-30 00:01:18 3 F 0 2017-12-30 00:01:18 2017-12-30 00:01:18 > library(dplyr) 2017-12-30 00:01:18 2017-12-30 00:01:18 Attaching package: 'dplyr' 2017-12-30 00:01:18 2017-12-30 00:01:18 The following objects are masked from 'package:stats': 2017-12-30 00:01:18 2017-12-30 00:01:18 filter, lag 2017-12-30 00:01:18 2017-12-30 00:01:18 The following objects are masked from 'package:base': 2017-12-30 00:01:18 2017-12-30 00:01:18 intersect, setdiff, setequal, union 2017-12-30 00:01:18 2017-12-30 00:01:18 2017-12-30 00:01:18 > dplyr_db <- MonetDBLite::src_monetdblite(dbdir) 2017-12-30 00:01:18 2017-12-30 00:01:18 > nppes_tbl <- tbl(dplyr_db, "npi") 2017-12-30 00:01:18 QQ: 'SELECT * 2017-12-30 00:01:18 FROM "npi" AS "zzz1" 2017-12-30 00:01:18 WHERE (0 = 1)' 2017-12-30 00:01:19 II: Finished in 0.13s 2017-12-30 00:01:19 2017-12-30 00:01:19 > nppes_tbl %>% summarize(mean = mean(provider_enumeration_year)) 2017-12-30 00:01:19 QQ: 'SELECT AVG("provider_enumeration_year") AS "mean" 2017-12-30 00:01:19 FROM "npi" 2017-12-30 00:01:19 LIMIT 10' 2017-12-30 00:01:19 II: Finished in 0.11s 2017-12-30 00:01:19 # Source: lazy query [?? x 1] 2017-12-30 00:01:19 # Database: MonetDBEmbeddedConnection 2017-12-30 00:01:19 mean 2017-12-30 00:01:19 2017-12-30 00:01:19 1 2009.771 2017-12-30 00:01:19 2017-12-30 00:01:19 > nppes_tbl %>% group_by(provider_gender_code) %>% summarize(mean = mean(provider_enumeration_year)) 2017-12-30 00:01:19 QQ: 'SELECT "provider_gender_code", AVG("provider_enumeration_year") AS "mean" 2017-12-30 00:01:19 FROM "npi" 2017-12-30 00:01:19 GROUP BY "provider_gender_code" 2017-12-30 00:01:19 LIMIT 10' 2017-12-30 00:01:19 II: Finished in 0.42s 2017-12-30 00:01:20 # Source: lazy query [?? x 2] 2017-12-30 00:01:20 # Database: MonetDBEmbeddedConnection 2017-12-30 00:01:20 provider_gender_code mean 2017-12-30 00:01:20 2017-12-30 00:01:20 1 M 2008.700 2017-12-30 00:01:20 2 2009.681 2017-12-30 00:01:20 3 F 2010.503 2017-12-30 00:01:20 2017-12-30 00:01:20 > dbGetQuery(db, "SELECT COUNT(*) FROM npi") 2017-12-30 00:01:20 QQ: 'SELECT COUNT(*) FROM npi' 2017-12-30 00:01:20 II: Finished in 0.14s 2017-12-30 00:01:20 L3 2017-12-30 00:01:20 1 5420942 2017-12-30 00:01:20 2017-12-30 00:01:20 > dbDisconnect(db, shutdown = TRUE) 2017-12-30 00:01:22 2017-12-30 00:01:22 > dbdir <- file.path("F:/r-wd/nppes-1514585893", "MonetDB") 2017-12-30 00:01:22 2017-12-30 00:01:22 > warnings() 2017-12-30 00:01:22 NULL 2017-12-30 00:01:22 2017-12-30 00:01:22 > library(DBI) 2017-12-30 00:01:22 2017-12-30 00:01:22 > db <- dbConnect(MonetDBLite::MonetDBLite(), dbdir) 2017-12-30 00:04:41 2017-12-30 00:04:41 > cs <- dbGetQuery(db, " select tables.name, columns.name, location from tables inner join columns on tables.id=columns.table_id left join storage on ..." ... [TRUNCATED] 2017-12-30 00:04:41 QQ: ' select tables.name, columns.name, location from tables inner join columns on tables.id=columns.table_id left join storage on tables.name=storage.table and columns.name=storage.column where location is null and tables.name not in ('tables', 'columns', 'users', 'querylog_catalog', 'querylog_calls', 'querylog_history', 'tracelog', 'sessions', 'optimizers', 'environment', 'queue', 'rejects', 'storage', 'storagemodel', 'tablestoragemodel') ' 2017-12-30 00:04:41 II: Finished in 0.14s 2017-12-30 00:04:41 2017-12-30 00:04:41 > print(cs) 2017-12-30 00:04:41 [1] name name location 2017-12-30 00:04:41 <0 rows> (or 0-length row.names) 2017-12-30 00:04:41 2017-12-30 00:04:41 > stopifnot(nrow(cs) == 0) 2017-12-30 00:04:41 2017-12-30 00:04:41 > dbDisconnect(db, shutdown = TRUE) 2017-12-30 00:04:41 > test return code=0