0

I am working through the samples in this document from Microsoft. Linux Foundation Delta Lake overview

The sections on table creation work of Notebooks works in that I have tables created in the default database as expected in accordance with the samples.

I have also created a delta table in another Lake Database that I have previously created. This too is successful using

%%sql
CREATE TABLE avsales.creditcardDelta
USING DELTA 
LOCATION '/delta/delta-table-703038'

If I try to query this in the notebook I get good results

%%pyspark
df = spark.sql("SELECT * FROM `avsales`.`creditcarddelta`")
df.show(10)
CreditCardID CardType CardNumber ExpMonth ExpYear ModifiedDate
1 SuperiorCard 33332664695310 11 2006 2013-07-29 00:00:...
2 Distinguish 55552127249722 8 2005 2013-12-05 00:00:...
3 ColonialVoice 77778344838353 7 2005 2014-01-14 00:00:...
4 ColonialVoice 77774915718248 7 2006 2013-05-20 00:00:...
5 Vista 11114404600042 4 2005 2013-02-01 00:00:...
6 Distinguish 55557132036181 9 2006 2014-04-10 00:00:...
7 Distinguish 55553635401028 6 2007 2013-02-01 00:00:...
8 SuperiorCard 33336081193101 7 2007 2013-06-30 00:00:...
9 Distinguish 55553465625901 2 2005 2013-09-23 00:00:...
10 SuperiorCard 33332126386493 8 2008 2011-08-31 00:00:...

However if I try to run a SQL script on the table I get an error.

SELECT TOP (10) [CreditCardID]
,[CardType]
,[CardNumber]
,[ExpMonth]
,[ExpYear]
,[ModifiedDate]
 FROM [avsales].[dbo].[creditcarddelta]

returns

Started executing query at Line 1
Invalid object name 'avsales.dbo.creditcarddelta'.
Total execution time: 00:00:02.307

This is true of any SQL from default as well.

Has anyone managed to get this to work? If so what did I miss?

Steve-at-sword
  • 63
  • 1
  • 11
  • 1
    Remove `.[dbo].` and see what happens. – jarlh Nov 22 '21 at 09:36
  • That syntax is invalid for example this query of a parquet table in the same database returns values correctly: ``` SELECT TOP (100) [CountryRegionCode] ,[CurrencyCode] ,[ModifiedDate] ,[_corrupt_record] FROM [avsales].[dbo].[countryregioncurrency] ``` – Steve-at-sword Nov 22 '21 at 09:53
  • 1
    Still a bit odd . You create the table `avsales.creditcardDelta`,and when you select from the table `[avsales].[dbo].[creditcarddelta]` - you get the error _Invalid object name 'avsales.dbo.creditcarddelta'_. – jarlh Nov 22 '21 at 09:57
  • so.. what error do you get when you run `SELECT * FROM avsales.creditcarddelta`? – Nick.Mc Nov 22 '21 at 13:08
  • The error I get is Invalid object name 'avsales.creditcarddelta' but similarly I get Invalid object name 'avsales.countryregioncurrency'. where as this works SELECT TOP (100) * FROM [avsales].[dbo].[countryregioncurrency] – Steve-at-sword Nov 23 '21 at 10:26
  • @jarlh its not odd its the difference between sparksql and SQL. This is pretty well documented and honestly a distraction. – Steve-at-sword Nov 23 '21 at 10:28

0 Answers0