about how to use MySQL's Group_concat () function on Vertica

Today, when customers are using Vertica, they want to use the Group_concat () function of MySQL.

The Group_concat function returns a string result that is composed of concatenated values in the grouping. Since this function is not in the Vertica. So you need to develop it yourself. Fortunately, someone has already written the third-party expansion pack. Here is a brief description of how to use it in Vertica.

The first thing to do is to download an expansion pack that implements the Group_concat () function, here it is. Download and copy to Vertica server.

Unzip the package with the root user.

[[email protected]] Unzip Vertica-package.zip

Change the owner of this folder.

[[email protected]] Chown-r dbadmin:verticadba vertica-package/

Switch to the Dbadmin user.

[[email protected]] Su-dbadm

Go to the Unpacked folder.

[[email protected]] CD vertica-package/

There are many expansion packs under the file, we just need to string-package this package because the package contains Group_concat () function

Go to this folder

[Email protected] vertica-package]$ CD strings_package/

By readme.md, you need to compile and install it first.

If it fails to compile: Execute the following statement:

[Email protected]]yum-y Groupinstall "Development tools" && yum-y groupinstall "Additional development"

[[email protected] strings_package]$ Make[[email protected] strings_package]$ make install

Prompt for the following information even if the installation is successful.

The next test is:

To connect to a database:

Input:

Dbadmin=> Select Group_concat (node_name) over () from nodes;

Output Result:

Then test the following statement:

Dbadmin=> Select Schema_name,projection_name,group_concat (node_name) over (partition by Schema_name,projection_ Name) from (select distinct node_name,schema_name,projection_name to Storage_containers) sc order by schema_name, Proje Ction_name;

Output Result:

You can see that it has implemented the functionality of MySQL's Group_concat ().

about how to use MySQL's Group_concat () function on Vertica