cancel
Showing results for 
Search instead for 
Did you mean: 
Community Discussions
cancel
Showing results for 
Search instead for 
Did you mean: 

Delta External table

tajinder123
New Contributor II

Hi I am new to databricks and need some inputs.

I am trying to create Delta External table in databricks using existing path which contains csv files.

What i observed is below code will create EXTERNAL table but provider is CSV.

-------------------------------

CREATE TABLE employee_csv1
(id STRING, first_name STRING, last_name STRING, email STRING, gender STRING, salary DOUBLE, team STRING)
USING CSV
OPTIONS(
   header = "true",
   delimeter  = ","
)
LOCATION "${dataset.employee}/FileStore/extracted_files/employee_details/csv_files"

---------------------------

Below code will create MANAGED table and here provider is delta.

------------------------------

CREATE TABLE employee123 AS
SELECT * FROM csv.`${dataset.employee}/FileStore/extracted_files/employee_details/csv_files`;

---------------------------

However i am trying to create EXTERNAL table where provider is delta where it uses existing path as location.

Can anybody guide or provide syntax on it if its possible.

1 ACCEPTED SOLUTION

Accepted Solutions

shan_chandra
Honored Contributor III
Honored Contributor III

@tajinder123  -  could you please try this? 

CREATE TABLE employee123 (id STRING, first_name STRING, last_name STRING, email STRING, gender STRING, salary DOUBLE, team STRING)
USING DELTA
LOCATION '/path/to/existing/delta/files';

 

View solution in original post

5 REPLIES 5

shan_chandra
Honored Contributor III
Honored Contributor III

@tajinder123 - can you please modify the syntax as below to create as a delta table

CREATE TABLE employee123
USING DELTA
LOCATION '/path/to/existing/delta/files';

 

@shan_chandra 

Thankyou, it worked but i am getting below. Can you please tell what need to be added along with it to read column names from header. Its in first line.

Also please tell how can we add schema manually. Thanks,

You are trying to read a Delta table `spark_catalog`.`default`.`employee1234` that does not have any columns.

shan_chandra
Honored Contributor III
Honored Contributor III

@tajinder123  -  could you please try this? 

CREATE TABLE employee123 (id STRING, first_name STRING, last_name STRING, email STRING, gender STRING, salary DOUBLE, team STRING)
USING DELTA
LOCATION '/path/to/existing/delta/files';

 

@shan_chandra THankyou, I just figured out same but i am glad you replied. Below i wrote and now i came and saw same thig. One thing I observed is that if files are already there in that location, create table wont work. It need to be empty, although we can use Insert into table statement and put data inside. Let me know if i am wrong here.

%sql
CREATE TABLE employee12345(id String,
first_name String,
last_name String,
email String,
gender String,
salary String,
team String)
USING DELTA
LOCATION '/dbfs/FileStore/extracted_files/employee_details/csv_files_1';

shan_chandra
Honored Contributor III
Honored Contributor III

@tajinder123 - can you please try the following

1. dropping the table first and followed by

2. dbutils.fs.rm to remove the files recursively from the dbfs location and  

3. create or replace command to recreate the table.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.