Create Table in Hive with one file

There are many possible solutions: 1) Add distribute by partition key at the end of your query. Maybe there are many partitions per reducer and each reducer creates files for each partition. This may reduce the number of files and memory consumption as well. hive.exec.reducers.bytes.per.reducer setting will define how much data each reducer will process. … Read more

PostgreSQL create table if not exists

This feature has been implemented in Postgres 9.1: CREATE TABLE IF NOT EXISTS myschema.mytable (i integer); For older versions, here is a function to work around it: CREATE OR REPLACE FUNCTION create_mytable() RETURNS void LANGUAGE plpgsql AS $func$ BEGIN IF EXISTS (SELECT FROM pg_catalog.pg_tables WHERE schemaname=”myschema” AND tablename=”mytable”) THEN RAISE NOTICE ‘Table myschema.mytable already exists.’; … Read more

MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB

I tried all the solutions here, but only this parameter innodb_strict_mode = 0 solved my day… From the manual: The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX statements. innodb_strict_mode also enables a record size check, so that an INSERT or UPDATE never fails due to the … Read more

Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table

“MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format.” This is from mysql site. You can store only this type, but you can use one of the many time format functions to change it, when you need to display it. Mysql Time and Date functions For example, one of those functions is the DATE_FORMAT, … Read more

Is it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?

http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis provides an overview of this issue from PostgreSQL’s perspective. Is DDL transactional according to this document? PostgreSQL – yes MySQL – no; DDL causes an implicit commit Oracle Database 11g Release 2 and above – by default, no, but an alternative called edition-based redefinition exists Older versions of Oracle – no; DDL causes an … Read more

Create a temporary table in a SELECT statement without a separate CREATE TABLE

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1) From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use … Read more

tech