Forum

Kognitio Console issues, advice, etc.
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

unexpected symbol near '..'

by MikeAtkinson » Tue Oct 29, 2013 9:53 am

I'm getting the following error when I'm running a Kog script which runs an external script on the database:

lua parse failure:
[string "script"]:79: unexpected symbol near '..'


My script is the following:

Code: Select all

--Set mod iterations
c={0,1,2,3,4,5,6,7,8,9};

--Create results table
drop table initial_forecast_results cascade;
create ram only table initial_forecast_results(Results varchar);

--Setup External Script
drop external script regression_forecasts;
create external script regression_forecasts environment RSCRIPT
receives(account_number varchar, row_id int,tot_weekend int, duration decimal(5,3),month_dme) partition by account_number order by account_number,row_id
sends (results varchar)
isolate partitions
script '
# Read in the data
options(error=expression(q("no")))
#sink("/tmp/log2.txt")
input_data <- read.csv(file=file("stdin"), header=FALSE)    
sink(, type="message")
#    
#Rename data and order
colnames(input_data) <-c("ID", "Row", "NumWendDays","Duration","Month")
#       
#Split data
#training<-input_data[which(input_data$Month<="2013-04-30"),]
#forecasting<-input_data[which(input_data$Month>"2013-04-30"),]
training<-input_data[which(input_data$Row<=24),]
forecasting<-input_data[which(input_data$Row>24),]
#    
#Build Regression Model
loglin<-lm(Duration ~ Row+NumWendDays, as.data.frame(training))
coe<-summary(loglin)$coefficients[,"Estimate"]
stderror<-summary(loglin)$coefficients[,"Std. Error"]
tval<-summary(loglin)$coefficients[,"t value"]
pval<-summary(loglin)$coefficients[,"Pr(>|t|)"]
#    
#Forecast and combine into table
Predictions<-predict(loglin,as.data.frame(forecasting))    
#    
#Holt-Winters
library("forecast")
durationseries <- ts(training$Duration,frequency=12,start=c(2011,8), end=c(2013,7))  
sfit <- HoltWinters(durationseries,season="mult")
nsfit <- HoltWinters(durationseries,gamma=FALSE,season="mult")    
sfit_pred <- predict(sfit, n.ahead = 12)
nsfit_pred <- predict(nsfit,n.ahead = 12)    
hpred1<-data.frame(sfit_pred)
hpred2<-data.frame(nsfit_pred)
#    
#Pasting together
#Note first set in Regcoeff is intercept, second is row_id and third is number of weekend days
cat(file="",paste("Regcoeff", input_data$ID[1], coe, stderror, tval, pval, sep="|", collapse="\n"), "\n")
cat(file="",paste("Sfit parameters",  input_data$ID[1], sfit$alpha,sfit$beta,sfit$gamma, sep="|", collapse="\n"), "\n")
cat(file="",paste("Nsfit parameters", input_data$ID[1], nsfit$alpha,nsfit$beta,nsfit$gamma, sep="|", collapse="\n"), "\n")
cat(file="", paste("Model_fit",input_data$ID[1], training$Month,training$NumWendDays,training$Duration,fitted(loglin),fitted(sfit)[,1], fitted(nsfit)[,1], sep="|",collapse="\n"),"\n")    
cat(file="", paste("Forecasts",input_data$ID[1], forecasting$Month,forecasting$NumWendDays,forecasting$Duration,Predictions, hpred1$fit, hpred2$fit, sep="|",collapse="\n"),"\n")
';

--Create view for each iteration
for i,remainder in pairs(c) do
    create or replace view iterative_forecast_data
    as
    select 
        a.account_number,
        a.row_id,
        a.tot_weekend,
        cast(coalesce(case when fau.duration<1 then 0 else ln(fau.duration) end,0) as dec(12,6)) duration,
        a.month_dme
    from
        (select account_number, fd.row_id, fd.month_dme, fd.tot_weekend
            from 
                (select distinct account_number from V_FORECAST_ACCOUNTs
                 where kog_id mod 10 = $remainder) a,
        V_Forecast_Dates fd) a
    left outer join
        V_FORECAST_ACCOUNTS_USAGE fau
        on a.account_number=fau.account_number
        and a.month_dme=fau.month_dme;

    --Pull view into RAM
    create view image iterative_forecast_data;
    
    --Call forecast script and write out results
    insert into initial_forecast_results
    select * from
        (external script regression_forecasts from
                (select  account_number, row_id, tot_weekend, duration, cast(month_dme as varchar) month_dme from iterative_forecast_data
                )) ext1
                ;           
end
;
Reply with quote Top
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: unexpected symbol near '..'

by MikeAtkinson » Tue Oct 29, 2013 10:14 am

This problem occurs because of the way Kog scripts include variables within SQL statements using the $.

A Kog script like:
v = 1;
select $v;
is transformed internally into the Lua statements:
v = 1;
sql("select " .. v);
this is true even within strings so:
v = 1;
select 'v=$v';
gives a resultset of 1 row with a column "v=1".

In the problem script there are several $ within the external script, some of which cause this error. Even worse if somevar is defined in the Kog script $somevar will cause a replacement when none is intended. This can cause hard to identify bugs in the external script.

solution
The solution is to use "here" strings, this is strings of the form S'unique( )unique' where "unique" can be replaced by any sequence of characters not in the string.

NOTE: always use "here" strings for external scripts, this ensures that $, ' and other characters are treated correctly.

Change the line:
script '
to:
script S'script(
and the line:
';
to:
)script';
and all will be well.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron