Forum

Kognitio Console issues, advice, etc.
Single Poster
Offline
User avatar
Posts: 1
Joined: Tue Jan 10, 2017 9:14 pm

1000 row limit for SELECT using kogscript.exe

by george.d.peterson » Tue Jan 10, 2017 9:20 pm

UPDATE: I've found that I can use wxunloader.exe to get all of the results, but I'm still curious if there's a way to get them from my .kog script using kogscript.exe.

Hello,

I apologize if this question has an easy answer. I have attempted to search.

The TLDR:
It appears that my SELECT queries, with results being written to a file by file:write, are limited to 1000 rows. What can I do to remedy this? Thank you.

Details:

I have a .kog script that I am running from kogscript.exe, and it has SELECT queries like the following:

Code: Select all

table = select * from $strTable order by 1,2;
That result is then written to a file like so

Code: Select all

file = io.open(strDirectoryName .. '\\' .. strFileName, "w+")
for i,row in ipairs(table.rows) do
    for j, k in ipairs(row) do
          if j >= #row then
            file:write(row[j]);
          else
            file:write(row[j], delim);
          end;
    end;
    if next(table.rows, i) ~= nil then
      file:write("\n");
    end;
  end;
end;
Reply with quote Top
Contributor
Offline
Posts: 183
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: 1000 row limit for SELECT using kogscript.exe

by MikeAtkinson » Mon Jan 16, 2017 11:43 am

Thanks for asking this question, it has shown up a defect in our script handling, in that there is no easy way to return more than 1000 rows from a query using the kogscript command line tool. As you have discovered, the best way of outputting CSV data from Kognitio is to use wxunloader.exe, this will be faster than using kogscript, has many options to handle special situations and is tested as part of Kognitio's QA.

When running kog scripts within Console the "Max rows:" field within the "Options" tab may be set, this allows up to 1,000,000 rows to be returned.

I've added a new global variable to kog scripts "max_rows", which when set will control the number of rows returned by queries. This will be added to a future version of Kognitio Console.

A future version of Console might also raise the limit above 1,000,000 rows. We are working on the ability to stream rows within kogscript, this will mean that the entire query results do not have to reside in RAM as a Lua table.

A future version of Kognitio Console will also contain a csv module which will allow code like:

Code: Select all

-- reading CSV data
f = io.open("C:/temp/test_data.csv");
csvreader = csv.reader(f)

for line, row in pairs(csvreader) do
    print("line=".. line.. ", values=(" .. row .. ")")
end

for line, x, y, z in csvreader:values() do
    print("line=".. line.. ", values=(" ..x .. "," .. y .. "," .. z .. ")")
end


-- writing a row as CSV to stdout
csvwriter = csv.writer(io.stdout);
row = {1, "a", 2}
csvwriter.writerow(row)
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 2 guests

cron