How to get Oracle create table statement in SQL*Plus
From Get table and index DDL the easy way:
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
spool off;
How do I use CREATE OR REPLACE?
This works on functions, procedures, packages, types, synonyms, trigger and views.
Update:
After updating the post for the third time, I'll reformulate this:
This does not work on tables :)
And yes, there is documentation on this syntax, and there are no REPLACE
option for CREATE TABLE
.
How to convert mysql DDL into hive DDL
I actually thought this was not supported, but after looking at the Source here is what I saw in HiveImport.java:
/**
* @return true if we're just generating the DDL for the import, but
* not actually running it (i.e., --generate-only mode). If so, don't
* do any side-effecting actions in Hive.
*/
private boolean isGenerateOnly() {
return generateOnly;
}
/**
* @return a File object that can be used to write the DDL statement.
* If we're in gen-only mode, this should be a file in the outdir, named
* after the Hive table we're creating. If we're in import mode, this should
* be a one-off temporary file.
*/
private File getScriptFile(String outputTableName) throws IOException {
if (!isGenerateOnly()) {
return File.createTempFile("hive-script-", ".txt",
new File(options.getTempDir()));
} else {
return new File(new File(options.getCodeOutputDir()),
outputTableName + ".q");
}
}
So basically you should be able to do only the DDL generation using the option --generate-only
used in cunjunction with --outdir
and your table will be create in the output dir specified and named after your table.
For example based on the link you provided:
sqoop import --verbose --fields-terminated-by ',' --connect jdbc:mysql://localhost/test --table employee --hive-import --warehouse-dir /user/hive/warehouse --fields-terminated-by ',' --split-by id --hive-table employee --outdir /tmp/mysql_to_hive/ddl --generate-only
will create /tmp/mysql_to_hive/ddl/employee.q
How to create hive table from Spark data frame, using its schema?
Assuming, you are using Spark 2.1.0 or later and my_DF is your dataframe,
//get the schema split as string with comma-separated field-datatype pairs
StructType my_schema = my_DF.schema();
String columns = Arrays.stream(my_schema.fields())
.map(field -> field.name()+" "+field.dataType().typeName())
.collect(Collectors.joining(","));
//drop the table if already created
spark.sql("drop table if exists my_table");
//create the table using the dataframe schema
spark.sql("create table my_table(" + columns + ")
row format delimited fields terminated by '|' location '/my/hdfs/location'");
//write the dataframe data to the hdfs location for the created Hive table
my_DF.write()
.format("com.databricks.spark.csv")
.option("delimiter","|")
.mode("overwrite")
.save("/my/hdfs/location");
The other method using temp table
my_DF.createOrReplaceTempView("my_temp_table");
spark.sql("drop table if exists my_table");
spark.sql("create table my_table as select * from my_temp_table");
Related Topics
Count of Non-Null Columns in Each Row
Find All Rows with Null Value(S) in Any Column
How to List Field's Name in Table in Access Using SQL
Oracle Connect by Clause Equivalent in SQL Server
How to Find the .Net Framework Version Used in an Ssis 2008 R2 Package
Foreign Key Column Mapped to Multiple Primary Keys
Oracle Autoincrement with Sequence and Trigger Is Not Working Correctly
Case Statement in SQL, How to Return Multiple Variables
How to Perform a Select Query in a Do Block
Return Rows from Insert with on Conflict Without Needing to Update
Does Facebook Fql Contain the SQL Like Operator