Skip to content

Instantly share code, notes, and snippets.

@paschott

paschott/Slack.sql

Created Jan 19, 2019
Embed
What would you like to do?
Creates SQL objects for Slack analysis and adjusts the usernames in the message text column
--Create table to store posts
CREATE TABLE [dbo].[Posts](
[Username] [nvarchar](255) NULL,
[Type] [nvarchar](255) NULL,
[Subtype] [nvarchar](255) NULL,
[TS] [nvarchar](255) NULL,
[Channel] [nvarchar](255) NOT NULL,
[MessageText] [nvarchar](max) NULL,
[MessageDate] [datetime2](7) NULL,
[PostID] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED
(
[PostID] ASC
)
)
GO
--Create table to store users for more meaningful text analysis
CREATE TABLE Users(UserID NVARCHAR(50), UserName NVARCHAR(100), RealName NVARCHAR(255))
GO
--Import the users separately
--expects the users.json in the given folder
DECLARE @JSON NVARCHAR(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET(BULK 'C:\SlackExport\users.json', SINGLE_CLOB) AS oro
INSERT dbo.Users ( UserID, UserName, RealName )
SELECT DISTINCT id, name, real_name
FROM OPENJSON(@JSON)
WITH (id nvarchar(50), name nvarchar(100), real_name nvarchar(255)) AS oj
--Create a Full-Text index on the MessageText table to allow for faster update to replace user id w/ user name
CREATE FULLTEXT CATALOG [Messages]
WITH ACCENT_SENSITIVITY = ON
GO
CREATE FULLTEXT INDEX ON [dbo].[Posts] (
MessageText Language 1033
)
KEY INDEX PK_Posts
ON [Messages]
WITH CHANGE_TRACKING AUTO
GO
/*
Populate the Posts table using the Powershell script
*/
--Run the following after populating the Posts table to replace the Slack UserID w/ the User Name
DECLARE UserCursor CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT UserId, UserName
FROM dbo.Users AS u
DECLARE @UserID NVARCHAR(50), @UserName NVARCHAR(100)
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @UserID, @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE Posts SET MessageText = REPLACE(MessageText, @UserID, @UserName)
WHERE CONTAINS(MessageText, @UserID)
FETCH NEXT FROM UserCursor INTO @UserID, @UserName
END
CLOSE UserCursor
DEALLOCATE UserCursor
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.