![]() This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. Otherwise, a more flexible approach is to replace the SQL's COPY command with the psql's "meta-command" called \copy which which takes all the same options as the "real" COPY, but is run inside the client (with no need for at the end): psql -c "\copy tbname FROM '/tmp/the_file.csv' delimiter '|' csv" You can then try out basic queries to fetch the various tables and map them to the Scala case classes: ctx.run(query)Ĭmd25.sc:1: SELECT x.id, x.name, x.countrycode, x.district, x.population FROM city xĬity(1, "Kabul", "AFG", "Kabol", 1780000),Ĭity(2, "Qandahar", "AFG", "Qandahar", 237500),Ĭity(3, "Herat", "AFG", "Herat", 186800),Ĭity(4, "Mazar-e-Sharif", "AFG", "Balkh", 127800),Ĭity(5, "Amsterdam", "NLD", "Noord-Holland", 731200),Ĭity(6, "Rotterdam", "NLD", "Zuid-Holland", 593321),Ĭity(7, "Haag", "NLD", "Zuid-Holland", ctx.run(query)Ĭmd26.sc:1: SELECT x.code, x.name, x.continent, x.region, x.surfacearea, x.indepyear, x.population, x.lifeexpectancy, x.gnp, x.gnpold, x.localname, x.governmentform, x.The solution in the accepted answer will only work on the server and when the user executing the query will have permissions to read the file as explained in this SO answer. Lastly, here is the mapping case class for the countrylanguage table: \d countrylanguageĬase class CountryLanguage(countrycode: String, ![]() Optional values which do not have a Postgres not null flag set are modeled using Scala Options Here is the mapping from table schema to case class for the city table: \d city There are also SnakeCase, UpperCase, or CamelCase name mappers you can use if your database uses those naming conventions.ĭefining the case classes representing each table is straightforward. To map between these two conventions, we had earlier defined our Quill ctx to use a LowerCase name mapper: val ctx = new PostgresJdbcContext(LowerCase, new HikariDataSource(config)) ![]() This particular Postgres database uses a lowercase variable name convention, which doesn't quite match Scala's PascalCase for class names and camelCase for field names. As a first approximation, the mapping is as follows: Postgres Quill expects tables to be represented by Scala case classes, with individual columns within each table mapping to primitive data types. "countrylanguage_countrycode_fkey" FOREIGN KEY (countrycode) REFERENCES country(code) "countrylanguage_pkey" PRIMARY KEY, btree (countrycode, language) | language | character varying | not null | TABLE "countrylanguage" CONSTRAINT "countrylanguage_countrycode_fkey" FOREIGN KEY (countrycode) \d countrylanguage "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id) | governmentform | character varying | not null | ![]() | localname | character varying | not null | | region | character varying | not null | | continent | character varying | not null | TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES \d country | district | character varying | not null | | countrycode | character(3) | not null | | public | countrylanguage | table | postgres \d city PG-CLI uses the standard \d command to list tables and columns: \d The sample data used in the world database is Copyright StatisticsĪnd imported into our postgres database using PG-CLI: \i world.sql PostgreSQL port of the MySQL "World" database. In a separate terminal, we will be using the open source PG-CLI tool to directly connect to our test Postgres database:Īfter installation, you can connect PG-CLI to the local test database via pgcli -U postgres: $ pgcli -U postgresĪs sample data for this exercise, we will be using a Postgres version of the popular world.sql dataset:. ![]() setUser above, but for now we will mostly stick the defaults. The HikariConfig and PGSimpleDataSource classes expose most of the things you can configure when connecting to the Postgres database, e.g. Val ctx = new PostgresJdbcContext(LowerCase, new HikariDataSource(config))Ĭtx will be our primary interface to the Postgres database through Quill. Val pgDataSource = new .PGSimpleDataSource() On top of Ammonite, we will also install the Quill database query library, and the OpenTable Embedded Postgres database library: import $ivy. If you like Ammonite, please support our development at Setup To begin with, we will be using the Ammonite Scala REPL: $ sudo sh -c '(echo "#!/usr/bin/env sh" & curl -L ) > /usr/local/bin/amm & chmod +x /usr/local/bin/amm' & amm ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |