{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/14086","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/14086/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/14086/comments","events_url":"https://api.github.com/repos/apache/spark/issues/14086/events","html_url":"https://github.com/apache/spark/pull/14086","id":164259945,"number":14086,"title":"[SPARK-16463][SQL] Support `truncate` option in Overwrite mode for JDBC DataFrameWriter","user":{"login":"dongjoon-hyun","id":9700541,"avatar_url":"https://avatars.githubusercontent.com/u/9700541?v=3","gravatar_id":"","url":"https://api.github.com/users/dongjoon-hyun","html_url":"https://github.com/dongjoon-hyun","followers_url":"https://api.github.com/users/dongjoon-hyun/followers","following_url":"https://api.github.com/users/dongjoon-hyun/following{/other_user}","gists_url":"https://api.github.com/users/dongjoon-hyun/gists{/gist_id}","starred_url":"https://api.github.com/users/dongjoon-hyun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dongjoon-hyun/subscriptions","organizations_url":"https://api.github.com/users/dongjoon-hyun/orgs","repos_url":"https://api.github.com/users/dongjoon-hyun/repos","events_url":"https://api.github.com/users/dongjoon-hyun/events{/privacy}","received_events_url":"https://api.github.com/users/dongjoon-hyun/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":37,"created_at":"2016-07-07T08:52:01Z","updated_at":"2016-07-18T13:21:39Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/14086","html_url":"https://github.com/apache/spark/pull/14086","diff_url":"https://github.com/apache/spark/pull/14086.diff","patch_url":"https://github.com/apache/spark/pull/14086.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\nThis PR adds a boolean option, `truncate`, for `SaveMode.Overwrite` of JDBC DataFrameWriter. If this option is `true`, it use `TRUNCATE TABLE` instead of `DROP TABLE`. This is a trivial option, but will provide great **convenience** for BI tool users based on RDBMS tables generated by Spark.\r\n\r\n**Goal**\r\n- Without `CREATE/DROP` privilege, we can save dataframe to database. Sometime these are not allowed for security.\r\n- It will preserve the existing table information, so users can add and keep some additional `INDEX` and `CONSTRAINT`s for the table.\r\n- Sometime, `TRUNCATE` is faster than the combination of `DROP/CREATE`.\r\n\r\n**Before (TABLE with INDEX case)**: SparkShell & MySQL CLI are interleaved intentionally.\r\n```scala\r\nscala> val (url, prop)=(\"jdbc:mysql://localhost:3306/temp?useSSL=false\", new java.util.Properties)\r\nscala> prop.setProperty(\"user\",\"root\")\r\nscala> df.write.mode(\"overwrite\").jdbc(url, \"table_with_index\", prop)\r\nscala> spark.range(10).write.mode(\"overwrite\").jdbc(url, \"table_with_index\", prop)\r\nmysql> DESC table_with_index;\r\n+-------+------------+------+-----+---------+-------+\r\n| Field | Type       | Null | Key | Default | Extra |\r\n+-------+------------+------+-----+---------+-------+\r\n| id    | bigint(20) | NO   |     | NULL    |       |\r\n+-------+------------+------+-----+---------+-------+\r\nmysql> CREATE UNIQUE INDEX idx_id ON table_with_index(id);\r\nmysql> DESC table_with_index;\r\n+-------+------------+------+-----+---------+-------+\r\n| Field | Type       | Null | Key | Default | Extra |\r\n+-------+------------+------+-----+---------+-------+\r\n| id    | bigint(20) | NO   | PRI | NULL    |       |\r\n+-------+------------+------+-----+---------+-------+\r\nscala> spark.range(10).write.mode(\"overwrite\").jdbc(url, \"table_with_index\", prop)\r\nmysql> DESC table_with_index;\r\n+-------+------------+------+-----+---------+-------+\r\n| Field | Type       | Null | Key | Default | Extra |\r\n+-------+------------+------+-----+---------+-------+\r\n| id    | bigint(20) | NO   |     | NULL    |       |\r\n+-------+------------+------+-----+---------+-------+\r\n```\r\n\r\n**After (TABLE with INDEX case)**\r\n```scala\r\nscala> spark.range(10).write.mode(\"overwrite\").option(\"truncate\", true).jdbc(url, \"table_with_index\", prop)\r\nmysql> DESC table_with_index;\r\n+-------+------------+------+-----+---------+-------+\r\n| Field | Type       | Null | Key | Default | Extra |\r\n+-------+------------+------+-----+---------+-------+\r\n| id    | bigint(20) | NO   | PRI | NULL    |       |\r\n+-------+------------+------+-----+---------+-------+\r\n```\r\n\r\n## How was this patch tested?\r\n\r\nPass the Jenkins tests with a updated testcase."},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/233326311","html_url":"https://github.com/apache/spark/pull/14086#issuecomment-233326311","issue_url":"https://api.github.com/repos/apache/spark/issues/14086","id":233326311,"user":{"login":"dongjoon-hyun","id":9700541,"avatar_url":"https://avatars.githubusercontent.com/u/9700541?v=3","gravatar_id":"","url":"https://api.github.com/users/dongjoon-hyun","html_url":"https://github.com/dongjoon-hyun","followers_url":"https://api.github.com/users/dongjoon-hyun/followers","following_url":"https://api.github.com/users/dongjoon-hyun/following{/other_user}","gists_url":"https://api.github.com/users/dongjoon-hyun/gists{/gist_id}","starred_url":"https://api.github.com/users/dongjoon-hyun/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dongjoon-hyun/subscriptions","organizations_url":"https://api.github.com/users/dongjoon-hyun/orgs","repos_url":"https://api.github.com/users/dongjoon-hyun/repos","events_url":"https://api.github.com/users/dongjoon-hyun/events{/privacy}","received_events_url":"https://api.github.com/users/dongjoon-hyun/received_events","type":"User","site_admin":false},"created_at":"2016-07-18T13:21:39Z","updated_at":"2016-07-18T13:21:39Z","body":"I think it's valuable to explicitly compare `truncate` option with the existing `SaveMode.Overwrite` in popular DBMSs.\r\nAfter some testing, we can provide this with more clear limitation notes if exists."}}
{"actor":{"display_login":"dongjoon-hyun"}}