Thread: Visual query builder for PosgreSQL?
Hi, I'm looking for some good visual query builder which can be used by non-tech people for some ETL tasks. Do you have any recommendation? Now, we're moving our data from Excel to PostgreSQL to deal with large amount of data, and we need to process some ETL tasks, with using JOIN and GROUP BY between tables, up to 10 tables on it. Of course, I can write ad-hoc queries by myself. However, I'd like to allow non-tech people to issue ad-hoc queries with using some visual query builder. I have already looked a query builder feature in pgAdminIII, but I'm not sure whether I can use GROUP BY with it. Do you have any experience or recommendation about visual query builder for PostgreSQL? Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
> I'm looking for some good visual query builder which can be used by non-t= ech people for some ETL tasks. Do you have any recommendation? > > Now, we're moving our data from Excel to PostgreSQL to deal with large am= ount of data, and we need to process some ETL tasks, with using JOIN and GR= OUP BY between tables, up to 10 tables on it. > > Of course, I can write ad-hoc queries by myself. However, I'd like to all= ow non-tech people to issue ad-hoc queries with using some visual query bui= lder. > > I have already looked a query builder feature in pgAdminIII, but I'm not = sure whether I can use GROUP BY with it. > > Do you have any experience or recommendation about visual query builder f= or PostgreSQL? You can do joins in the PGAdmin3 query builder but I'm fairly sure you can'= t do group by's. Apparently, SQL Manager from here: http://www.sqlmanager.net/en/products/postgresql/manager/ Has a "Visual query builder allowing you to build complicated queries witho= ut any knowledge of SQL syntax" I may be shot for even suggesting this (and it is a bit of a hassle) but yo= u could recreate your table structure in MS Access and use its graphical qu= ery builder to generate your SQL. It does allow for group by's, counts, etc= . Regards, Russell Keane INPS Follow us on twitter | visit www.inps.co.uk
Satoshi Nagayasu <snaga@uptime.jp> wrote:=0A=0A> Of course, I can write ad-= hoc queries by myself. However, I'd=0A> like to allow non-tech people to is= sue ad-hoc queries with using=0A> some visual query builder.=0A=0AYou shoul= d probably take a look at http://htsql.org/=0A=0AIt is free open source sof= tware intended for "accidental=0Aprogrammers" -- people who want to pull su= mmarized data from a=0Adatabase without learning SQL or needing rigorous tr= aining.=A0 Its=0Adevelopment was partially funded by grants from foundation= s,=0Aincluding the National Science Foundation.=A0 It does support=0APostgr= eSQL and most definitely support counts, sums, etc.=A0 In fact,=0Ait can au= tomagically give you pretty summary graphs with the=0Aability to drill down= to supporting detail.=0A=0A-Kevin=0A
On 02/08/2013 07:33 AM, Kevin Grittner wrote: > Satoshi Nagayasu <snaga@uptime.jp> wrote: > >> Of course, I can write ad-hoc queries by myself. However, I'd >> like to allow non-tech people to issue ad-hoc queries with using >> some visual query builder. > > You should probably take a look at http://htsql.org/ > > It is free open source software intended for "accidental > programmers" -- people who want to pull summarized data from a > database without learning SQL or needing rigorous training. Its > development was partially funded by grants from foundations, > including the National Science Foundation. It does support > PostgreSQL and most definitely support counts, sums, etc. In fact, > it can automagically give you pretty summary graphs with the > ability to drill down to supporting detail. I second this. I have been trying it out and it is proving quite useful. The interesting part is that if you use the HTML interface you can get the SQL sent to the server, helps you learn that also. > > -Kevin > > > -- Adrian Klaver adrian.klaver@gmail.com
> I'm looking for some good visual query builder which can be used by > non-tech people for some ETL tasks. Do you have any recommendation? Libreoffice Base? I know it has a visual query builder and it does support PostgreSQL, but I have no clue what it is worth, since I tend to use SQL instead. Sincerely, Wolfgang
On Fri, 2013-02-08 at 11:37 +0000, Russell Keane wrote: > > I'm looking for some good visual query builder which can be used by non-tech people for some ETL tasks. Do you have anyrecommendation? > > > > Now, we're moving our data from Excel to PostgreSQL to deal with large amount of data, and we need to process some ETLtasks, with using JOIN and GROUP BY between tables, up to 10 tables on it. > > > > Of course, I can write ad-hoc queries by myself. However, I'd like to allow non-tech people to issue ad-hoc queries withusing some visual query builder. > > > > I have already looked a query builder feature in pgAdminIII, but I'm not sure whether I can use GROUP BY with it. > > > > Do you have any experience or recommendation about visual query builder for PostgreSQL? > > You can do joins in the PGAdmin3 query builder but I'm fairly sure you can't do group by's. > Correct. The pgAdmin3 graphical query builder doesn't support GROUP BY. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
(2013/02/08 20:37), Russell Keane wrote: >> I'm looking for some good visual query builder which can be used by non-tech people for some ETL tasks. Do you have anyrecommendation? >> >> Now, we're moving our data from Excel to PostgreSQL to deal with large amount of data, and we need to process some ETLtasks, with using JOIN and GROUP BY between tables, up to 10 tables on it. >> >> Of course, I can write ad-hoc queries by myself. However, I'd like to allow non-tech people to issue ad-hoc queries withusing some visual query builder. >> >> I have already looked a query builder feature in pgAdminIII, but I'm not sure whether I can use GROUP BY with it. >> >> Do you have any experience or recommendation about visual query builder for PostgreSQL? > > You can do joins in the PGAdmin3 query builder but I'm fairly sure you can't do group by's. > > Apparently, SQL Manager from here: > http://www.sqlmanager.net/en/products/postgresql/manager/ > Has a "Visual query builder allowing you to build complicated queries without any knowledge of SQL syntax" Thanks. I have started trying "SQL Query for PostgreSQL" from EMS, the company provides SQL Manager. I think "SQL Query" is the subset of the SQL Manager, and would be suitable for our purpose. (and a bit cheaper :) > I may be shot for even suggesting this (and it is a bit of a hassle) but you could recreate your table structure in MSAccess and use its graphical query builder to generate your SQL. It does allow for group by's, counts, etc. Yeah, I will also look at MS Access as a query builder, although I'm not familiar with the software so far. Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
(2013/02/09 0:41), Adrian Klaver wrote: > On 02/08/2013 07:33 AM, Kevin Grittner wrote: >> Satoshi Nagayasu <snaga@uptime.jp> wrote: >> >>> Of course, I can write ad-hoc queries by myself. However, I'd >>> like to allow non-tech people to issue ad-hoc queries with using >>> some visual query builder. >> >> You should probably take a look at http://htsql.org/ >> >> It is free open source software intended for "accidental >> programmers" -- people who want to pull summarized data from a >> database without learning SQL or needing rigorous training. Its >> development was partially funded by grants from foundations, >> including the National Science Foundation. It does support >> PostgreSQL and most definitely support counts, sums, etc. In fact, >> it can automagically give you pretty summary graphs with the >> ability to drill down to supporting detail. > > I second this. I have been trying it out and it is proving quite useful. > The interesting part is that if you use the HTML interface you can get > the SQL sent to the server, helps you learn that also. Very interesting. If non-tech people can learn a simple query language for their analytics purpose, it would be worth trying. I think some "abstraction layer" is needed between non-tech users and DBMS to allow them to issue queries themselves. I think some query builder could be one of the solutions, and also some simple query language could be another solution. I will look into it. Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
Many, many (many) years ago, while working at DIGITAL EQUIPMENT (before it = bellied up), I worked with a relational DB they created called "RDB". Some= one at DEC wrote an sql development gui in Xwindows called "InstantSQL". I= t was really great. All the tables of the DB were icons, you could drag th= e ones you wanted into the main workspace, open them up, graphically select= the cols you wanted to "select" and join tables by click-dragging a line = from one col of one table to another. You could build your predicate by dr= agging out search criteria boxes frmo the colmns, filling them in with the = criteria, changing the logical operators, etc... . It supported aggregate = functions, order by, and manual editing of the resulting SQL if you wanted = to. The users LOVED that thing. They would build their queries, run them = and redirect output to a file. Then "save" the query for future use. They= would also use it to prototype sql which they would then cut/paste into sc= ripts. Very intuitive to use and very user friendly. =20 Other guis allow you to do some simple things in a gui, like join tables, b= ut I've yet to see something that let the user do it all in the gui. The s= ingle gui approach had some advantages, the most important of which (IMO) w= as ease of use and being so intuitive.=20 When Oracle "acquired" RDB, InstantSQL died with the DB (after the canabali= zed it). Haven't seen InstantSQL since. They're probably just sitting on = the patent. I just wish good-ole InstantSQL was still out there. Your use= rs, like mine of the past, would probably love it. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgr= esql.org] On Behalf Of Satoshi Nagayasu Sent: Friday, February 15, 2013 8:53 PM To: Adrian Klaver Cc: Kevin Grittner; pgsql-general@postgresql.org Subject: Re: [GENERAL] Visual query builder for PosgreSQL? (2013/02/09 0:41), Adrian Klaver wrote: > On 02/08/2013 07:33 AM, Kevin Grittner wrote: >> Satoshi Nagayasu <snaga@uptime.jp> wrote: >> >>> Of course, I can write ad-hoc queries by myself. However, I'd like=20 >>> to allow non-tech people to issue ad-hoc queries with using some=20 >>> visual query builder. >> >> You should probably take a look at http://htsql.org/ >> >> It is free open source software intended for "accidental programmers"=20 >> -- people who want to pull summarized data from a database without=20 >> learning SQL or needing rigorous training. Its development was=20 >> partially funded by grants from foundations, including the National=20 >> Science Foundation. It does support PostgreSQL and most definitely=20 >> support counts, sums, etc. In fact, it can automagically give you=20 >> pretty summary graphs with the ability to drill down to supporting=20 >> detail. > > I second this. I have been trying it out and it is proving quite useful. > The interesting part is that if you use the HTML interface you can get=20 > the SQL sent to the server, helps you learn that also. Very interesting. If non-tech people can learn a simple query language for their analytics pu= rpose, it would be worth trying. I think some "abstraction layer" is needed between non-tech users and DBMS = to allow them to issue queries themselves. I think some query builder could be one of the solutions, and also some sim= ple query language could be another solution. I will look into it. Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp --=20 Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Gauthier, Dave wrote on 16.02.2013 17:04: > Many, many (many) years ago, while working at DIGITAL EQUIPMENT > (before it bellied up), I worked with a relational DB they created > called "RDB". RDB/VMS was actually the first relational database I ever worked with. Boy, is that a long time ago...
2013/02/17 1:17, Thomas Kellerer wrote: > Gauthier, Dave wrote on 16.02.2013 17:04: >> Many, many (many) years ago, while working at DIGITAL EQUIPMENT >> (before it bellied up), I worked with a relational DB they created >> called "RDB". > > RDB/VMS was actually the first relational database I ever worked with. > > Boy, is that a long time ago... I have never seen InstantSQL itself, but I had chances several times to go the RDB technical seminars here in Japan, where I have found that learning database technology is really exciting. :) So, I wish I will be able to work with RDB (and VMS) someday. :) Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
Satoshi Nagayasu, 17.02.2013 17:42: > I have never seen InstantSQL itself, but I had chances several times > to go the RDB technical seminars here in Japan, where I have found > that learning database technology is really exciting. :) > > So, I wish I will be able to work with RDB (and VMS) someday. :) That most probably will never happen though. Oracle has effectively killed RDB and HP has effectively killed VMS.