alasql / node-red-contrib-alasql Goto Github PK
View Code? Open in Web Editor NEWA Node-RED node wrapping AlaSQL for fast SQL based in-memory data processing for BI and ERP applications.
License: MIT License
A Node-RED node wrapping AlaSQL for fast SQL based in-memory data processing for BI and ERP applications.
License: MIT License
It is relatively easy in node-red to query relational databases (such as Oracle, MS, MySql) and update No-Sql databases (such as Mongo, Couch, Raven) . But the reverse is more difficult - inserting/updating data resulting from a No-Sql database to a relational database.
In the current release of node-red alasql, the SQL output format - 'INSERT INTO table_name VALUES (value1, value2, value3, ...);' was depreciated (by my request ;( ) due to requiring some significant mods to the alasql node to operate properly for the SQL output format.
AlaSql is perfect for that task. In the next few weeks I will be changing the alasql node to be able to query the results from No-Sql databases and produce the INSERT INTO statements required to update relational databases.
Any suggestions or comments appreciated!
@PotOfCoffee2Go
Hi, i've a problem with this node, and the google groups comunity suggested that i post the problem here, so i'll put the link of the issue in here. Thanks.
https://groups.google.com/forum/#!topic/node-red/sZ1tVTDDHhc
Before please undo commit 4e9ac45
I have a query that works perfectly if I put it in the query window. But if I try to move the filename to msg.payload (using the inject node) and use either FROM ?
or FROM $0
, it doesn't work. There is no error but zero records are returned.
This works:
SELECT *
FROM 'C:/Users/xxxxx/Staff List for COVID19 21Sep20.csv'
WHERE Employee_Last_Name = "Knight"
This does not (where msg.payload = 'C:/Users/xxxxx/Staff List for COVID19 21Sep20.csv') :
SELECT *
FROM ?
WHERE Employee_Last_Name = "Knight"
Neither does this:
SELECT *
FROM $0
WHERE Employee_Last_Name = "Knight"
This, however, does work (where msg.payload = 'Knight'):
SELECT *
FROM 'C:/Users/xxxxx/Staff List for COVID19 21Sep20.csv'
WHERE Employee_Last_Name = $0
This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.
These updates have all been created already. Click a checkbox below to force a retry/rebase of any.
package.json
alasql 4.5.2
fs-extra 11.1.1
There is no option to handle errors like path to file not exists or bad file format, am I right?
AlaSQL node is very good but I'm having difficulty performing a UNION ALL when I do a Select on two spreadsheets (or two sheets from one spreadsheet). Node-red crashes with an uncaught exception error ...
30 Aug 11:23:53 - [red] Uncaught Exception:
30 Aug 11:23:53 - TypeError: Cannot read property 'data' of undefined
at queryfn3 (C:\Users\IBM_ADMIN.node-red\node_modules\alasql\dist\alasql.fs.js:6401:11)
at queryfn2 (C:\Users\IBM_ADMIN.node-red\node_modules\alasql\dist\alasql.fs.js:6305:9)
at C:\Users\IBM_ADMIN.node-red\node_modules\alasql\dist\alasql.fs.js:15680:10
at C:\Users\IBM_ADMIN.node-red\node_modules\alasql\dist\alasql.fs.js:3591:21
at C:\Users\IBM_ADMIN.node-red\node_modules\npm\node_modules\graceful-fs\graceful-fs.js:78:16
at FSReqWrap.readFileAfterClose [as oncomplete] (fs.js:445:3)
I can read data from individual spreadsheets or tables but if I try the UNION it fails.
Here's my SQL from within the node..
select * from xlsx("c:\union.xlsx")
union all
select * from xlsx("c:\union2.xlsx")
These are small (2 lines) worksheets and are exactly the same format (NAME, SURNAME, ID)
Hello,
I have an xls format spreadsheet that I upload via dropbox.
The output of node is a buffer. It would be possible to use the buffer as input from node alafile in
Hi there, could I please ask that you remove the node-red keyword from the package.json until this actually works? That will remove it from the flows listing and prevent people from having problems by installing it before it is ready.
Thanks.
I'm trying to use external moment.js lib without luck. Here is my code:
CREATE FUNCTION getWeek AS
``
function(x) {
let moment = global.get('moment');
return moment(x).week();
}
``;
SELECT
COUNT(*),
SUBSTRING(created_at,1,10) AS created_at,
getWeek(created_at)
FROM ?
GROUP BY SUBSTRING(created_at,1,10)
I failed also when tried to acceess msg object. Is there a way to resolve it?
EDITED:
As a workaround I used moment in function node before using alasql node so I can work with "ready" data.
let moment = global.get('moment');
for (let i = 0; i < msg.payload.length; i++) {
msg.payload[i].week = moment(msg.payload[i].created_at).week(),
msg.payload[i].month = moment(msg.payload[i].created_at).month(),
msg.payload[i].yaer = moment(msg.payload[i].created_at).year()
}
return msg;
I'm not sure if this is a problem due to my lack of understanding of json and that stuff but here goes anyway.
I send an inventory of machines and their postcodes to a node called Postcode Lookup with the result that it returns the geographical coordinates of the machine from its postcode. This works fine and I now want to save the resulting list of positions ...
If I use the "file" node with the action "append to file" selected, I get a file with the machine listing showing id, latitude and longitude.
However, if I try to use Alafile out node (which is excellent btw!) , let's say to create a csv or an Excel file, I just get one record saved to the csv or xls file (I think it's the last machine in the list) which suggests that the node is overwriting rather than appending to the new file ... which is what I want. Have I missed something or is there an issue with my input to Alafile?
thanks!
Hi guys,
maybe someone of you can help me.
I'm working with AlaSQL wrapper for node-red, and it seems that JOIN condition doesn't work at all.
Let's start with something simple (from the examples):
var data = { COLORS: [[1,"red"],[2,"yellow"],[3,"orange"]], "FRUITS":[[1,"apple"],[2,"banana"],[3,"orange"]]};
msg.payload=[data.COLORS, data.FRUITS];
return msg;
SELECT MATRIX COLORS.[0], COLORS.[1], FRUITS.[1] AS [2] FROM ? AS COLORS JOIN ? AS FRUITS USING [0]
TypeError: Cannot read property 'length' of undefined
Does anyone of you have an example of a working join condition with AlaSQL in node-red ?
Here are the complete flow code
[{"id":"d8ea2581.c9eaa8","type":"alasql","z":"44bba25d.edd63c","name":"","query":"SELECT MATRIX COLORS.[0], COLORS.[1], FRUITS.[1] AS [2] FROM ? AS COLORS JOIN ? AS FRUITS USING [0]","x":910,"y":80,"wires":[["4e58c548.dcf80c"]]},{"id":"e1180ede.b9d41","type":"function","z":"44bba25d.edd63c","name":"","func":"var data = {COLORS: [[1,\"red\"],[2,\"yellow\"],[3,\"orange\"]],\"FRUITS\":[[1,\"apple\"],[2,\"banana\"],[3,\"orange\"]]};\n\nmsg.payload=[data.COLORS, data.FRUITS];\n\nreturn msg;","outputs":1,"noerr":0,"x":790,"y":80,"wires":[["d8ea2581.c9eaa8"]]},{"id":"4e58c548.dcf80c","type":"debug","z":"44bba25d.edd63c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1030,"y":80,"wires":[]},{"id":"a887efce.c7898","type":"inject","z":"44bba25d.edd63c","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":660,"y":80,"wires":[["e1180ede.b9d41"]]}]
Is there a specific reason the query node has no outputs? It seems odd since you put the results of the query into the msg.payload. Maybe I am not quite understanding the intent of this node. We are looking to use the node inside a larger flow that is essentially performing ETL type functions.
Hi @agershun and @mathiasrw :)
Developers on my staff are interfacing to kiosk machines running node-red, with flows which publish mqtt topics to our main HQ. At HQ we are implementing node-red to continue the flow which updates our databases, ERP, and PIM systems. But since the HQ dev staff is more knowledgeable in SQL than JavaScript (we're getting there :) - your node-red-contrib-alasql
nodes are the best thing since slice bread!
I have forked your repo and am in the process of implementing/testing the following changes to my fork of node-red-contrib-alasq
within the next few weeks. I have other experiences and features of using alasql nodes 'in the wild' that you might be interested in for future consideration.
Your input would be greatly appreciated, and hopefully you might be interested pulling some of these changes into your repo if I were to submit a PR?
The core "file in" and "file out" node(s) info is :
The filename can be configured in the node. If left blank it should be set by msg.filename on the incoming message.Ala file in/out
nodes should work similarly - thus works as a drop-in replacement for the corefile in/out
nodes.
ala file in/out
- ability to pass name of in/out filename in msg.filename.While building flows that use
ala file in/out
- it is handy to have the node-red editor inform devs that the payload contains data and the name of the file the data is read/output.
The
ala file out
is shown before theala file in
node with-in the node-red palette - should be afterala file in
.
ala file in
displays before ala file out
in the node-red editor palette.Visually, the
ala file out
should have the alasql icon on the right side of the node display. By convention, nodes with with an input connector but no output connector (an output node) normally locates the icon on the right side of the node display.
ala file out
node from the left to the right side of the node displayNode background colors for file in/out in the node-red editor palette should be the same. The
alasql
node background (under advanced tab) to remain a slightly darker color to be visually different from theala file
nodes.
ala file in/out
nodes to the same background color.During development, the node-red editor should display the filename of the file read or written.
ala file in/out
read/wrote.TIA for your consideration and advice!,
I'm getting this error when trying run below query
CREATE TABLE t
(
report_type string,
inv_qty number,
week number,
month number,
year number
);
INSERT INTO t(report_type,inv_qty,week,month,year) VALUES ((SELECT 'k',1,2,3,4));
SELECT * FROM t;
Hi! I'm appreciate your great node.
I would like to use updated v2.0.0
because I'm interested at this.topic
or this.query
active parameters function. I think that it is nice idea!
I installed at https://flows.nodered.org/node/node-red-contrib-alasql using Node-RED GUI pallete. But It was v1.1.0
in 2020/5/14.
Could you update to v2.0.0
as npm version if you can? I'm looking forward to upgraded v2.0.0
and that function.
Query is working fine as expected on msg.payload if we use $0, but in case of array if we use $1 gives error.
🚨 You need to enable Continuous Integration on all branches of this repository. 🚨
To enable Greenkeeper, you need to make sure that a commit status is reported on all branches. This is required by Greenkeeper because it uses your CI build statuses to figure out when to notify you about breaking changes.
Since we didn’t receive a CI status on the greenkeeper/initial
branch, it’s possible that you don’t have CI set up yet. We recommend using Travis CI, but Greenkeeper will work with every other CI service as well.
If you have already set up a CI for this repository, you might need to check how it’s configured. Make sure it is set to run on all new branches. If you don’t want it to run on absolutely every branch, you can whitelist branches starting with greenkeeper/
.
Once you have installed and configured CI on this repository correctly, you’ll need to re-trigger Greenkeeper’s initial pull request. To do this, please delete the greenkeeper/initial
branch in this repository, and then remove and re-add this repository to the Greenkeeper App’s white list on Github. You'll find this list on your repo or organization’s settings page, under Installed GitHub Apps.
Hi!
I find out that msg.topic
is mentioned in the readMe of alasql node, but it isn't used in the code. A made a change on the code at my local. I changed in the readMe msg.topic
to msg.query
because msg.topic
used bz several another nodes. And I changed the node-red-contrib-alasql.js line 9 from var sql = this.query || 'SELECT * FROM ?';
to var sql = this.query || msg.query || 'SELECT * FROM ?';
to use this variable. It works fine for me. Please let me know how can it be released?
Thank you very much, Gábor
SELECT NULL AS 'col1'
returns msg.payload:
[{}]
It's intended behavior?
EDITED:
As workaround I'm using empty string to not lost object properties.
Hi,
When I tried to select from a json object, it didn't work properly.
Am I doing it wrong?
Sorry to trouble you, but please try the following flow.
version is 2.0.1
[{"id":"9710bc666e27f8ca","type":"alasql","z":"89cdb3c1eff17690","name":"SELECT","query":"-- SELECT * FROM ? WHERE age >= 17;\nSELECT * FROM ?","x":300,"y":1060,"wires":[["7dbd635df236fddb"]]},{"id":"7cec6519f9bab538","type":"inject","z":"89cdb3c1eff17690","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"name\":\"ken\",\"age\":19},{\"name\":\"rei\",\"age\":18}]","payloadType":"json","x":130,"y":1060,"wires":[["9710bc666e27f8ca"]]},{"id":"7dbd635df236fddb","type":"debug","z":"89cdb3c1eff17690","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":490,"y":1060,"wires":[]}]
Array of parameters passed in msg.payload is treated by alasql in insert as one parameter, ie.:
[{"id":"c1a70029.98ea7","type":"alasql","z":"2d89d4b5.a581bc","name":"","query":"CREATE TABLE ArticleGroups (Code string, Name string);","x":250,"y":1120,"wires":[["67e913ab.e7446c"]]},{"id":"af2252b0.09bc6","type":"inject","z":"2d89d4b5.a581bc","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":1120,"wires":[["c1a70029.98ea7"]]},{"id":"67e913ab.e7446c","type":"function","z":"2d89d4b5.a581bc","name":"","func":"msg.payload = [\"fd\",\"df\"];\nreturn msg;","outputs":1,"noerr":0,"x":400,"y":1120,"wires":[["23c7e6be.9f2bea"]]},{"id":"23c7e6be.9f2bea","type":"alasql","z":"2d89d4b5.a581bc","name":"","query":"INSERT INTO ArticleGroups VALUES (?,?);\nSELECT * FROM ArticleGroups;","x":550,"y":1120,"wires":[["d4e3b5c3.4e4e28","d43d6056.198fb"]]},{"id":"d4e3b5c3.4e4e28","type":"alasql","z":"2d89d4b5.a581bc","name":"","query":"DROP TABLE ArticleGroups;","x":710,"y":1120,"wires":[[]]},{"id":"d43d6056.198fb","type":"debug","z":"2d89d4b5.a581bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":700,"y":1060,"wires":[]}]
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.