can we create a new table from an existing table with data in pyspark

Question

Create table syntax for Teradata:

create table <DBname>.<Tablename>
as
select * from <DBname>.<Tablename>
with data;

In a similar way, how can we create a table in Spark SQL?

Answer 1

It's almost same in Spark SQL as well.

Example:

CREATE TABLE tablename 
    STORED AS PARQUET LOCATION 'some/location/incase/of/external/table' 
AS
SELECT *
    FROM source_table
WHERE 1=1

Regular Expression: (High level)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [COMMENT col_comment], ...)]
  [COMMENT table_comment]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
  ]
  [LOCATION path_to_save]
  [AS select_statement]

BTW, Spark supports more of the Hive syntax and features. you can refer CTAS doc here