https://www.getdaft.io logo
Join Slack
Powered by
<@U071FMQUR7W> <@U07AK4V9L0K> I'm working on estab...
# daft-dev
r
@Cory Grinstead @Desmond Cheong I'm working on establishing the DESCRIBE semantics and would like your feedback on this proposal. Neither ANSI-SQL nor PostgreSQL have a precedent for viewing table statistics. The closest I have found is the DuckDB SUMMARIZE Statement or the **ANALYZE + DESCRIBE FORMATTED** pattern – I prefer the single statement. ** • Option 1 – We introduce a SUMMARIZE Statement (or similar) and keep DESCRIBE vanilla SQL. • Option 2 – We support ANALYZE + DESCRIBE FORMATTED which only gains consistency with Hive-SQL as-fas-as I know. • 
DESCRIBE EXTENDED
is used to describe relational level statistics rather than column level statistics which suggests it is not the correct fit for the summarizing column statistics. Option 1 is to my personal taste, and is also technically simpler. Desmond has already PR'd a command for returning column statistics, while I have been working on the SQL DESCRIBE. Some more details in the 🧵
🙌 1
daft party 1
The DESCRIBE Statement produces a relation representing the schema of the relation being described. Doing 
print(df.schema())
will ultimately call the Display trait of Schema which makes a pretty-printed table. Rather than having DESCRIBE just return this string, we want it to return a relation.
Copy code
// <http://PySchema.rs|PySchema.rs> <- _PySchema.py <- Schema.py <- df.schema()
  
impl PySchema {

    // ...

    pub fn __repr__(&self) -> PyResult<String> {
        Ok(format!("{}", self.schema))
    }
}

// Display for Schema
#[display("{}\n", make_schema_vertical_table(
    fields.iter().map(|(name, field)| (name.clone(), field.dtype.to_string()))
))]
impl Schema { }
Copy code
DESCRIBE TABLE tbl;

-- output schema
-- +--------------+------------+
-- | column(text) | type(text) |
-- +--------------+------------+

DESCRIBE (SELECT * FROM 'data.csv')
usage
Copy code
DESCRIBE [TABLE] <table>;  -- df.describe()
DESCRIBE <query>;          -- df.describe()

SUMMARIZE <table>;         -- df.summarize()
d
DESCRIBE Statement produces a relation representing the schema of the relation being described.
Yeah I'm a bigger fan of this than shoving stats into the describe. Seems pretty standard to put name + type (+ nullable + default + collation + key info (?)) Hadn't done a competitor analysis when I wrote the PR to display stats (I was a little influenced by DBR's DESCRIBE EXTENDED, and spark syntax in general). Fine with SUMMARIZE
r
Seems pretty standard to put name + type (+ nullable + default + collation + key info (?))
yes, coming from SQL spec. I notices DBR's (and others) describe extended include additional rows rather than additional columns like SUMMARIZE does. • Supports DESCRIBE with an “EXTENDED” to return additional metadata about the relation and is a slightly different statement than the SQL DESCRIBE. • The documentation suggests this includes “additional information” like column stats, but the example outputs look different. The DESCRIBE EXTENDED “Returns additional metadata such as parent schema, owner, access time etc." • The Databricks DESCRIBE EXTENDED actually includes more ROWS rather than more COLUMNS. We should prefer to include more columns because (1) it demarcates the schema columns (which become rows in describe) from metadata information and (2) the SQL standard makes room for additional output columns of DESCRIBE so we are actually better aligning with the standard here. • Databricks has ANALYZE https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-aux-analyze-table.html which is a bit more similar and aligns with PostgreSQL to some degree.
Ok, sounds like we agree. I'll check with Cory as well, but for you PR let's go with
Copy code
df.summarize() # 1

# OR

df.describe(summarize=True) # 2
I'm inclined to #1
👍 2
d
ah 
ANALYZE
is a little different because it also enriches the catalog with statistics for the table (or refreshes stats if they are out of date). I'd say that's a different class of command
r
^ totally which is why I want to hold off on that front
d
I'm inclined to #1
+1
☝️ 1
r
Left one comment too about pivoting the output relation. This will make programmatic access easier and will mirror DESCRIBE. Ultimately it would be nice to converge the two, but I don't think the stability or vision is clear yet so separation makes "experimental" API flagging and depr. easier
4 Views
Open in Slack
PreviousNext
Powered by